[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO l_tot_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_loc_id;
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_loc_id
AND qty_type = 'LOADED';
SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
INTO l_tot_qty
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = p_subinventory_code
AND current_locator_id = p_loc_id;
SELECT Count(DISTINCT msn.serial_number)
INTO l_loaded_sys_qty
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id and
mtlt.lot_number is null) or
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
and mtlt.lot_number is not null))
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_loc_id
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.task_type <> 2
AND wdt.status = 4;
SELECT NVL((Trunc(SYSDATE) - Trunc(Max(count_date_current))),l_opp_cyc_count_days)
INTO l_no_of_days
FROM mtl_cycle_count_entries
WHERE subinventory = p_subinventory_code
AND inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND locator_id = p_loc_id
AND ENTRY_STATUS_CODE NOT IN (1,3);
SELECT 'Y'
INTO l_item_exists
FROM mtl_cycle_count_items
WHERE cycle_count_header_id = inv_cache.fromsub_rec.opp_cyc_count_header_id
AND inventory_item_id = p_inventory_item_id;
PROCEDURE delete_existing_cyc_count
(p_organization_id IN NUMBER ,
p_subinventory IN VARCHAR2 ,
p_locator_id IN NUMBER ,
p_inventory_item_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_existing_cyc_count';
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id IN (SELECT CYCLE_COUNT_ENTRY_ID
FROM mtl_cycle_count_entries
WHERE ENTRY_STATUS_CODE IN (1,3)
AND ORGANIZATION_ID=p_organization_id
AND SUBINVENTORY=p_subinventory
AND LOCATOR_ID=p_locator_id
AND INVENTORY_ITEM_ID=p_inventory_item_id)
AND ORGANIZATION_ID=p_organization_id;
Mdebug ( l_api_name||' : *** Deleted '||sql%rowcount||' WDT records' , g_message);
DELETE FROM mtl_cycle_count_entries
WHERE ENTRY_STATUS_CODE IN (1, 3)
AND ORGANIZATION_ID=p_organization_id
AND SUBINVENTORY=p_subinventory
AND LOCATOR_ID=p_locator_id
AND INVENTORY_ITEM_ID=p_inventory_item_id;
Mdebug ( l_api_name||' : *** Deleted '||sql%rowcount||' MCCE records' , g_message);
END delete_existing_cyc_count;
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO l_tot_qty
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_loc_id
AND ( (p_parent_lpn_id IS NOT NULL
AND NVL ( containerized_flag, 2 ) = 1)
AND lpn_id = p_parent_lpn_id
OR (p_parent_lpn_id IS NULL
AND NVL ( containerized_flag, 2 ) = 2)
)
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory_code
AND locator_id = p_loc_id
AND qty_type = 'LOADED'
AND ( (p_parent_lpn_id IS NOT NULL
AND NVL ( containerized_flag, 2 ) = 1)
AND NVL ( lpn_id, NVL ( content_lpn_id, -1 ) ) = p_parent_lpn_id
OR (p_parent_lpn_id IS NULL
AND NVL ( containerized_flag, 2 ) = 2)
)
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
INTO l_tot_qty
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = p_subinventory_code
AND current_locator_id = p_loc_id
AND ( (p_parent_lpn_id IS NOT NULL
AND lpn_id = p_parent_lpn_id)
OR (p_parent_lpn_id IS NULL
AND lpn_id IS NULL)
)
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number);
SELECT Count(DISTINCT msn.serial_number)
INTO l_loaded_sys_qty
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id and
mtlt.lot_number is null) or
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
and mtlt.lot_number is not null))
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory_code
AND mmtt.locator_id = p_loc_id
AND ( (p_parent_lpn_id IS NOT NULL
AND NVL ( mmtt.lpn_id, NVL ( content_lpn_id, -1 ) ) = p_parent_lpn_id)
OR (p_parent_lpn_id IS NULL
AND NVL ( mmtt.lpn_id, NVL ( content_lpn_id, -1 ) ) = -1)
)
AND ( mtlt.lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR msn.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.revision = mmtt.revision
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.task_type <> 2
AND wdt.status = 4;
SELECT Nvl(Sum(Decode(count_uom_current,
inv_cache.item_rec.primary_uom_code,
adj_cnt_qty,
(inv_convert.inv_um_convert(p_inventory_item_id,
5,
adj_cnt_qty,
count_uom_current,
inv_cache.item_rec.primary_uom_code,
NULL,
NULL
)
)
)
),0)
INTO l_cnt_qty
FROM (SELECT count_uom_current, (system_quantity_current - count_quantity_current) adj_cnt_qty
FROM mtl_cycle_count_entries
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory_code
AND locator_id = p_loc_id
AND cycle_count_header_id = g_cycle_count_header_id
AND entry_status_code = 2
AND ( (p_parent_lpn_id IS NOT NULL
AND parent_lpn_id = p_parent_lpn_id)
OR (p_parent_lpn_id IS NULL
AND parent_lpn_id IS NULL)
)
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
AND system_quantity_current <> count_quantity_current
UNION ALL
SELECT mcce.count_uom_current, (mcce.count_quantity_current - mcce.system_quantity_current) adj_cnt_qty
FROM mtl_cycle_count_entries mcce, mtl_serial_numbers msn
WHERE mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.cycle_count_header_id = g_cycle_count_header_id
AND mcce.entry_status_code = 2
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR mcce.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
AND mcce.serial_number = msn.serial_number
AND msn.inventory_item_id = mcce.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mcce.organization_id
AND msn.current_subinventory_code = p_subinventory_code
AND msn.current_locator_id = p_loc_id
AND ( ( msn.lot_number = p_lot_number
AND msn.lot_number = mcce.lot_number
)
OR p_lot_number IS NULL
)
AND NVL ( msn.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND NVL ( mcce.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND ( mcce.subinventory <> msn.current_subinventory_code
OR mcce.locator_id <> msn.current_locator_id
)
AND mcce.system_quantity_current <> mcce.count_quantity_current);
SELECT NVL ( SUM ( primary_transaction_quantity ), 0 )
INTO x_system_quantity
FROM MTL_ONHAND_QUANTITIES_DETAIL
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory
AND locator_id = p_locator_id
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT NVL ( SUM ( quantity ), 0 )
INTO l_loaded_sys_qty
FROM WMS_LOADED_QUANTITIES_V
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory_code = p_subinventory
AND locator_id = p_locator_id
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND qty_type = 'LOADED';
SELECT NVL ( SUM ( DECODE ( current_status, 3, 1, 0 ) ), 0 )
INTO x_system_quantity
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND current_subinventory_code = p_subinventory
AND current_locator_id = p_locator_id
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' );
SELECT Count(DISTINCT msn.serial_number)
INTO l_loaded_sys_qty
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
mtlt.lot_number is null) OR
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number = p_lot_number)
)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory
AND mmtt.locator_id = p_locator_id
AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND mmtt.transaction_temp_id=wdt.transaction_temp_id
AND NVL(wdt.status, 1) = 4;
SELECT Nvl(Sum(Decode(count_uom_current,
inv_cache.item_rec.primary_uom_code,
adj_cnt_qty,
(inv_convert.inv_um_convert(p_inventory_item_id,
5,
adj_cnt_qty,
count_uom_current,
inv_cache.item_rec.primary_uom_code,
NULL,
NULL
)
)
)
),0)
INTO l_cnt_qty
FROM (SELECT count_uom_current, (system_quantity_current - count_quantity_current) adj_cnt_qty
FROM mtl_cycle_count_entries
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND subinventory = p_subinventory
AND locator_id = p_locator_id
AND cycle_count_header_id = g_cycle_count_header_id
AND entry_status_code = 2
AND ( lot_number = p_lot_number
OR p_lot_number IS NULL
)
AND NVL ( revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
AND system_quantity_current <> count_quantity_current
UNION ALL
SELECT mcce.count_uom_current, (mcce.count_quantity_current - mcce.system_quantity_current) adj_cnt_qty
FROM mtl_cycle_count_entries mcce, mtl_serial_numbers msn
WHERE mcce.inventory_item_id = p_inventory_item_id
AND mcce.organization_id = p_organization_id
AND mcce.cycle_count_header_id = g_cycle_count_header_id
AND mcce.entry_status_code = 2
AND (p_from_Serial_number IS NULL OR p_to_Serial_number IS NULL OR mcce.serial_number BETWEEN p_from_Serial_number AND p_to_Serial_number)
AND mcce.serial_number = msn.serial_number
AND msn.inventory_item_id = mcce.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mcce.organization_id
AND msn.current_subinventory_code = p_subinventory
AND msn.current_locator_id = p_locator_id
AND ( ( msn.lot_number = p_lot_number
AND msn.lot_number = mcce.lot_number
)
OR p_lot_number IS NULL
)
AND NVL ( msn.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND NVL ( mcce.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND ( mcce.subinventory <> msn.current_subinventory_code
OR mcce.locator_id <> msn.current_locator_id
)
AND mcce.system_quantity_current <> mcce.count_quantity_current);
This procedure will get the total allocated pending qty for the selected item and SKU.
x_alloc_cur - A cursor which will give the pending allocations for the selected item and SKU ordered by priority.
x_allocated_qty - Total allocated pending qty for the selected item and SKU ordered by priority.
*/
PROCEDURE get_allocated_qty
(p_organization_id IN NUMBER ,
p_subinventory IN VARCHAR2 ,
p_locator_id IN NUMBER := NULL ,
p_parent_lpn_id IN NUMBER := NULL ,
p_inventory_item_id IN NUMBER ,
p_revision IN VARCHAR2 := NULL ,
p_lot_number IN VARCHAR2 := NULL ,
p_from_serial_number IN VARCHAR2 := NULL ,
p_to_serial_number IN VARCHAR2 := NULL ,
x_alloc_cur OUT NOCOPY t_genref,
x_allocated_qty OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'get_allocated_qty';
SELECT NVL ( SUM ( Nvl(mtlt.primary_quantity, mmtt.primary_quantity) ), 0 )
INTO l_allocated_pri_qty
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_transaction_lots_temp mtlt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
AND mmtt.subinventory_code = p_subinventory
AND mmtt.locator_id = p_locator_id
AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND NVL ( mtlt.lot_number, 'XX' ) = NVL ( p_lot_number, 'XX' )
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) <> 4;
SELECT mmtt.TRANSACTION_TEMP_ID, Nvl(Nvl(mtlt.primary_quantity, mmtt.primary_quantity), 0) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_transaction_lots_temp mtlt
WHERE mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
AND mmtt.subinventory_code = p_subinventory
AND mmtt.locator_id = p_locator_id
AND NVL ( mmtt.revision, 'XXX' ) = NVL ( p_revision, 'XXX' )
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND NVL ( mtlt.lot_number, 'XX' ) = NVL ( p_lot_number, 'XX')
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
SELECT Count(DISTINCT msn.serial_number)
INTO l_allocated_pri_qty
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
mtlt.lot_number IS NULL) OR
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number IS NOT NULL)
)
AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory
AND mmtt.locator_id = p_locator_id
AND nvl(mtlt.lot_number,'@@@') = nvl(p_lot_number,'@@@')
AND nvl(mmtt.revision,'##') = nvl(p_revision,'##')
AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND (p_parent_lpn_id IS NULL OR NVL(msn.lpn_id, -1) = p_parent_lpn_id)
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) <> 4;
SELECT DISTINCT mmtt.TRANSACTION_TEMP_ID, Count(DISTINCT msn.serial_number) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
mtlt.lot_number is null) OR
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number is not null)
)
AND (p_parent_lpn_id IS NULL OR NVL ( mmtt.allocated_lpn_id, NVL ( mmtt.lpn_id, NVL ( mmtt.content_lpn_id, -1 ) ) ) = p_parent_lpn_id)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND mmtt.subinventory_code = p_subinventory
AND mmtt.locator_id = p_locator_id
AND nvl(mtlt.lot_number,'@@@') = nvl(p_lot_number,'@@@')
AND nvl(mmtt.revision,'##') = nvl(p_revision,'##')
AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND (p_parent_lpn_id IS NULL OR NVL(msn.lpn_id, -1) = p_parent_lpn_id)
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
GROUP BY mmtt.TRANSACTION_TEMP_ID, mmtt.TASK_PRIORITY
ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
This procedure will get the total allocated pending serial qty for the selected item and SKU.
x_alloc_cur - A cursor which will give the pending allocations for the selected item and SKU ordered by priority.
x_allocated_qty - Total allocated pending qty for the selected item and SKU ordered by priority.
*/
PROCEDURE get_serial_allocated_qty
(p_organization_id IN NUMBER ,
p_inventory_item_id IN NUMBER ,
p_from_serial_number IN VARCHAR2 := NULL ,
p_to_serial_number IN VARCHAR2 := NULL ,
x_det_alloc_cur OUT NOCOPY t_genref,
x_det_allocated_qty OUT NOCOPY NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'get_serial_allocated_qty';
SELECT Count(DISTINCT msn.serial_number)
INTO l_allocated_pri_qty
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
mtlt.lot_number is null) OR
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number is not null)
)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) <> 4;
SELECT DISTINCT mmtt.TRANSACTION_TEMP_ID, Count(DISTINCT msn.serial_number) primary_quantity, Nvl(mmtt.TASK_PRIORITY,0)
FROM mtl_serial_numbers_temp msnt, mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt, mtl_serial_numbers msn, wms_dispatched_tasks wdt
WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id (+)
AND ((msnt.transaction_temp_id = mmtt.transaction_temp_id AND
mtlt.lot_number is null) OR
(msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND mtlt.lot_number is not null)
)
AND mmtt.inventory_item_id = p_inventory_item_id
AND mmtt.organization_id = p_organization_id
AND (p_from_serial_number IS NULL OR p_to_serial_number IS NULL OR msn.serial_number between p_from_serial_number AND p_to_serial_number)
AND msn.serial_number BETWEEN msnt.FM_SERIAL_NUMBER AND msnt.TO_SERIAL_NUMBER
AND msn.inventory_item_id = mmtt.inventory_item_id
AND msn.CURRENT_ORGANIZATION_ID=mmtt.organization_id
AND mmtt.transaction_temp_id=wdt.transaction_temp_id(+)
AND NVL(wdt.status, 1) NOT IN (3, 4, 9)
GROUP BY mmtt.TRANSACTION_TEMP_ID, mmtt.TASK_PRIORITY
ORDER BY Nvl(mmtt.TASK_PRIORITY,0);
a. Delete all the lpn level allocations for this lpn.
b. Delete all the serial level allocations for the serial item inside this lpn, if serial allocation is set as Yes.
c. Check for the total remaining qty at the locator level for each item inside the lpn, and delete the required allocations.
2. Counted serial is in a diff location.
a. Delete all the serial level allocations for the serials, if serial allocation is set as Yes.
b. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
3. Counted lpn in the same location.
a. Check for the total lpn level allocations for this lpn, and backorder the allocations till the allocated qty is <= the count qty.
b. If the counted item is serial controlled and if serial allocation is set as Yes, delete the required allocations if the system qty is less than count qty.
c. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
4. Counted serials in the same location.
a. If the counted item is serial controlled and if serial allocation is set as Yes, delete the required allocations if the system qty is less than count qty.
b. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
5. Counted for non serial loose qties in the same location.
a. Check for the total remaining qty at the locator level for the item, and delete the required allocations.
*/
PROCEDURE backorder_pending_tasks
(p_organization_id IN NUMBER ,
p_subinventory IN VARCHAR2 ,
p_locator_id IN NUMBER := NULL ,
p_parent_lpn_id IN NUMBER := NULL ,
p_inventory_item_id IN NUMBER ,
p_revision IN VARCHAR2 := NULL ,
p_lot_number IN VARCHAR2 := NULL ,
p_from_serial_number IN VARCHAR2 := NULL ,
p_to_serial_number IN VARCHAR2 := NULL ,
p_count_quantity IN NUMBER ,
p_count_uom IN VARCHAR2 ,
p_user_id IN NUMBER,
p_cost_group_id IN NUMBER := NULL,
p_secondary_uom IN VARCHAR2 := NULL,
p_secondary_qty IN NUMBER := NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'backorder_pending_tasks';
FOR ser_cur IN (SELECT current_subinventory_code, current_locator_id, serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number BETWEEN p_from_serial_number AND p_to_serial_number
AND (current_subinventory_code<>p_subinventory
OR current_locator_id<>p_locator_id)) LOOP
l_progress := '18';
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id ;
FOR lpn_contents_cur IN (SELECT DISTINCT wlc.inventory_item_id, wlc.lot_number, wlc.revision, DECODE(NVL(msn.serial_number, 'XXXX'), 'XXXX', wlc.primary_quantity, 1) primary_quantity, msn.serial_number
FROM wms_lpn_contents wlc, mtl_serial_numbers msn
WHERE wlc.parent_lpn_id=p_parent_lpn_id
AND wlc.inventory_item_id = msn.inventory_item_id (+)
AND ( msn.inventory_item_id IS NULL
OR (msn.current_organization_id = p_organization_id
AND msn.lpn_id=wlc.parent_lpn_id)))
LOOP
IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
IF (l_debug = 1) THEN
mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
FOR lpn_contents_cur IN (SELECT wlc.inventory_item_id, wlc.lot_number, wlc.revision, Sum(wlc.primary_quantity) primary_quantity
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id=p_parent_lpn_id
GROUP BY wlc.inventory_item_id, wlc.lot_number, wlc.revision)
LOOP
IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
IF (l_debug = 1) THEN
mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
FOR ser_cur IN (SELECT current_subinventory_code, current_locator_id, Count(DISTINCT serial_number) ser_cnt
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
AND serial_number BETWEEN p_from_serial_number AND p_to_serial_number
AND (current_subinventory_code<>p_subinventory
OR current_locator_id<>p_locator_id)
GROUP BY current_subinventory_code, current_locator_id) LOOP
l_progress := '193';
l_progress := 'delete_existing_cyc_count';
delete_existing_cyc_count
(p_organization_id => p_organization_id
, p_subinventory => p_subinventory
, p_locator_id => p_locator_id
, p_inventory_item_id => p_inventory_item_id);
l_progress := 'After delete_existing_cyc_count';
SELECT NVL ( subinventory_code, '###' ),
NVL ( locator_id, -99 ),
lpn_context
INTO l_lpn_subinv,
l_lpn_locator_id,
l_lpn_context
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_parent_lpn_id ;
FOR lpn_contents_cur IN (SELECT DISTINCT wlc.inventory_item_id, wlc.lot_number, wlc.revision, DECODE(NVL(msn.serial_number, 'XXXX'), 'XXXX', wlc.primary_quantity, 1) primary_quantity, msn.serial_number
FROM wms_lpn_contents wlc, mtl_serial_numbers msn
WHERE wlc.parent_lpn_id=p_parent_lpn_id
AND wlc.inventory_item_id = msn.inventory_item_id (+)
AND ( msn.inventory_item_id IS NULL
OR (msn.current_organization_id = p_organization_id
AND msn.lpn_id=wlc.parent_lpn_id)))
LOOP
IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
IF (l_debug = 1) THEN
mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
FOR lpn_contents_cur IN (SELECT wlc.inventory_item_id, wlc.lot_number, wlc.revision, Sum(wlc.primary_quantity) primary_quantity
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id=p_parent_lpn_id
GROUP BY wlc.inventory_item_id, wlc.lot_number, wlc.revision)
LOOP
IF (NOT inv_cache.Set_item_rec(p_organization_id => p_organization_id, p_item_id => lpn_contents_cur.inventory_item_id)) THEN
IF (l_debug = 1) THEN
mdebug(l_api_name||' : '||lpn_contents_cur.inventory_item_id||' is an invalid item',g_error);
l_progress := 'delete_existing_cyc_count';
delete_existing_cyc_count
(p_organization_id => p_organization_id
, p_subinventory => p_subinventory
, p_locator_id => p_locator_id
, p_inventory_item_id => p_inventory_item_id);
l_progress := 'After delete_existing_cyc_count';