[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_wdd
(x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_wdd_id IN NUMBER
,p_released_status IN VARCHAR2
,p_mol_id IN NUMBER
) IS
--l_detail_info_tab wsh_glbl_var_strct_grp.delivery_details_attr_tbl_type;
l_module_name := 'UPDATE_WDD';
print_debug('Entering update_wdd...',l_module_name,4);
l_in_rec.action_code := 'UPDATE';
print_debug('Calling wsh_interface_ext_grp.create_update_delivery_detail',l_module_name,4);
wsh_interface_ext_grp.create_update_delivery_detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec);
print_debug('Returned from wsh_interface_ext_grp.create_update_delivery_detail',l_module_name,4);
print_debug('wsh_interface_ext_grp.create_update_delivery_detail returned with error',l_module_name,4);
print_debug('wsh_interface_ext_grp.create_update_delivery_detail returned with success',l_module_name,4);
print_debug('Exitting update_wdd with the following values:',l_module_name,4);
END update_wdd;
print_debug('Calling update_wdd',l_module_name,4);
update_wdd
(x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_wdd_id => x_new_wdd_id
,p_released_status => NULL
,p_mol_id => p_new_mol_id);
print_debug('Returned from update_wdd',l_module_name,4);
print_debug('update_wdd returned with error',l_module_name,4);
print_debug('update_wdd returned with success',l_module_name,4);
PROCEDURE delete_reservation
(p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
,x_return_status OUT nocopy VARCHAR2)
IS
l_dummy_serial inv_reservation_global.serial_number_tbl_type;
l_module_name := 'DELETE_RESERVATION';
print_debug('Calling inv_reservation_pub.delete_reservation',l_module_name,4);
inv_reservation_pub.delete_reservation
(p_api_version_number => 1.0
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => p_rsv_rec
, p_serial_number => l_dummy_serial
);
print_debug('Returned from inv_reservation_pub.delete_reservation',l_module_name,4);
print_debug('Exiting delete_reservation with success',l_module_name,4);
print_debug('Exiting delete_reservation with error',l_module_name,4);
END delete_reservation;
, p_delete_flag => fnd_api.g_true
, p_sort_by_criteria => g_query_demand_ship_date_desc --???
);
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT 1
INTO l_dummy
FROM po_line_locations_all
WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
FOR UPDATE NOWAIT;
l_rsv_update_rec := l_rsv_results_tbl(i);
l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
,l_remaining_prim_qty
,l_rsv_results_tbl(i).primary_uom_code
,l_rsv_results_tbl(i).reservation_uom_code);
l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
,p_to_rsv_rec => l_rsv_update_rec
,x_new_rsv_id => l_reservation_id
,x_return_status => l_return_status);
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT 1
INTO l_dummy
FROM rcv_shipment_lines
WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
FOR UPDATE NOWAIT;
print_debug('Calling delete_reservation...',l_module_name,4);
delete_reservation
(p_rsv_rec => l_rsv_results_tbl(i)
,x_return_status => l_return_status
);
print_debug('Returned from delete_reservation',l_module_name,4);
l_rsv_update_rec := l_rsv_results_tbl(i);
l_rsv_update_rec.supply_source_type_id := g_source_type_po;
l_rsv_update_rec.supply_source_line_detail := NULL;
,p_to_rsv_rec => l_rsv_update_rec
,x_new_rsv_id => l_reservation_id
,x_return_status => l_return_status);
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT 1
INTO l_dummy
FROM rcv_shipment_lines
WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
FOR UPDATE NOWAIT;
SELECT 1
INTO l_dummy
FROM po_line_locations_all
WHERE line_location_id = p_cas_mol_rec_tb(1).po_line_location_id
FOR UPDATE NOWAIT;
SELECT requisition_header_id
INTO l_requisition_header_id
FROM po_requisition_lines_all
WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
FOR UPDATE NOWAIT;
SELECT 1
INTO l_dummy
FROM rcv_shipment_lines
WHERE requisition_line_id = p_cas_mol_rec_tb(1).requisition_line_id
AND shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
FOR UPDATE NOWAIT;
SELECT shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
FOR UPDATE NOWAIT;
l_rsv_update_rec := l_rsv_results_tbl(i);
l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
l_rsv_update_rec.primary_reservation_quantity := l_rsv_results_tbl(i).primary_reservation_quantity;
print_debug(' l_rsv_update_rec.primary_reservation_quantity => '||l_rsv_update_rec.primary_reservation_quantity,l_module_name,4);
--Relieve/delete reservation
l_progress := '###';
,p_prim_qty_to_relieve => l_rsv_update_rec.primary_reservation_quantity
,x_return_status => l_return_status
);
print_debug('Returned from delete_reservation',l_module_name,4);
l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
,l_remaining_prim_qty
,l_rsv_results_tbl(i).primary_uom_code
,l_rsv_results_tbl(i).reservation_uom_code);
--Depending on the routing, I will transfer/update released
--status later
l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
l_rsv_update_rec.demand_source_line_detail := NULL;
l_rsv_update_rec.supply_source_type_id := g_source_type_rcv;
l_rsv_update_rec.supply_source_header_id := NULL;
l_rsv_update_rec.supply_source_line_id := NULL;
l_rsv_update_rec.supply_source_line_detail := NULL;
print_debug('Calling update_wdd...',l_module_name,4);
update_wdd
(x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_wdd_id => l_rsv_update_rec.demand_source_line_detail
,p_released_status => 'R' --Ready to released
,p_mol_id => NULL
);
l_rsv_update_rec.demand_source_line_detail := NULL;
,p_to_rsv_rec => l_rsv_update_rec
,x_new_rsv_id => l_new_rsv_id
,x_return_status => l_return_status);
IF l_rsv_update_rec.demand_source_type_id IN (g_source_type_internal_ord,g_source_type_oe) THEN
set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
x_cas_mol_rec_tb,
l_rsv_update_rec.primary_reservation_quantity,
l_rsv_update_rec.demand_source_line_detail,
1
);
ELSIF (l_rsv_update_rec.demand_source_type_id = g_source_type_inv ) THEN
set_mol_wdd_tbl(p_cas_mol_rec_tb(1),
x_cas_mol_rec_tb,
l_rsv_update_rec.primary_reservation_quantity,
l_rsv_update_rec.demand_source_line_detail,
null
);
l_rsv_update_rec.primary_reservation_quantity,
l_rsv_update_rec.demand_source_line_detail,
2
);
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT 1
INTO l_dummy
FROM mtl_txn_request_lines
WHERE line_id = p_cas_mol_rec_tb(1).line_id
FOR UPDATE NOWAIT;
-- print_debug('Returned from update_wdd',l_module_name,4);
--2) The appropriate wdd are updated to STAGED status
--3) The appropriate reservation is transferred to INVENTORY (Check source_type columns)}}
BEGIN
SELECT Nvl(source_document_type_id, -1)
INTO l_doc_type
FROM wsh_delivery_details
WHERE delivery_detail_id = p_cas_mol_rec_tb(1).backorder_delivery_detail_id;
--Make sure that l_remaining_prim_qty are updated properly}}
IF (l_debug = 1) THEN
print_debug('l_remaining_prim_qty:'||l_remaining_prim_qty||
' i:'||i||
' rsv_id:'||l_rsv_results_tbl(i).reservation_id||
' prim_qty:'||l_rsv_results_tbl(i).primary_reservation_quantity||
' rsv_qty:'||l_rsv_results_tbl(i).reservation_quantity||
' uom_code:'||l_rsv_results_tbl(i).reservation_uom_code||
' orig_supply_src_type_id:'||l_rsv_results_tbl(i).orig_supply_source_type_id||
' demand_src_line_detail:'||l_rsv_results_tbl(i).demand_source_line_detail||
' ext_src_code:'||l_rsv_results_tbl(i).external_source_code
,l_module_name,4);
l_rsv_update_rec := l_rsv_results_tbl(i);
l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty
(p_cas_mol_rec_tb(1).inventory_item_id
,l_remaining_prim_qty
,l_rsv_results_tbl(i).primary_uom_code
,l_rsv_results_tbl(i).reservation_uom_code);
l_rsv_update_rec.demand_source_line_detail := l_new_wdd_id;
-- Lei's complete_crossdock API will update wdd
l_rsv_update_rec.supply_source_type_id := g_source_type_inv;
l_rsv_update_rec.supply_source_header_id := NULL;
l_rsv_update_rec.supply_source_line_id := NULL;
l_rsv_update_rec.supply_source_line_detail := NULL;
l_rsv_update_rec.demand_source_line_detail := NULL;
--6/30/05: Also update lpn/sub/loc info for xdock scenario
IF l_rsv_results_tbl(i).demand_source_line_detail IS NOT NULL THEN
l_rsv_update_rec.lpn_id := p_cas_mol_rec_tb(1).lpn_id;
l_rsv_update_rec.subinventory_code := p_cas_mol_rec_tb(1).subinventory_code;
l_rsv_update_rec.locator_id := p_cas_mol_rec_tb(1).locator_id;
l_rsv_update_rec.lot_number := p_cas_mol_rec_tb(1).lot_number;
l_rsv_update_rec.revision := p_cas_mol_rec_tb(1).item_revision;
l_rsv_update_rec.staged_flag := 'Y';
l_rsv_update_rec.crossdock_flag := NULL;
,p_to_rsv_rec => l_rsv_update_rec
,x_new_rsv_id => l_new_rsv_id
,x_return_status => l_return_status);
l_rsv_update_rec inv_reservation_global.mtl_reservation_rec_type;
SELECT SUM(primary_quantity)
INTO l_qty_with_no_wdd
FROM mtl_txn_request_lines mtrl
WHERE nvl(mtrl.lpn_id,-999)=nvl(p_cas_mol_rec_tb(1).lpn_id,-999)
AND nvl(mtrl.from_subinventory_code,'&&&')=nvl(p_cas_mol_rec_tb(1).from_subinventory_code,'&&&')--???
AND nvl(mtrl.from_locator_id,-999)=nvl(p_cas_mol_rec_tb(1).from_locator_id,-999)--???
AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND Nvl(mtrl.lot_number,'*&*') = Nvl(p_cas_mol_rec_tb(1).lot_number,'*&*')
AND nvl(mtrl.revision,'&&&') = nvl(p_cas_mol_rec_tb(1).item_revision,'&&&')
AND mtrl.line_status = 7
AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
AND (mtrl.quantity - Nvl(mtrl.quantity_delivered,0)) > 0
AND mtrl.backorder_delivery_detail_id IS NULL
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id);
FOR l_rsv_results IN (SELECT
reservation_id
, primary_uom_code
, primary_reservation_quantity
, reservation_uom_code
, demand_source_line_detail
FROM mtl_reservations mr
WHERE mr.supply_source_type_id = inv_reservation_global.g_source_type_rcv
AND mr.organization_id = p_cas_mol_rec_tb(1).organization_id
AND mr.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND ((mr.demand_source_line_detail IS NOT NULL
AND mr.demand_source_line_detail
IN (SELECT mol.backorder_delivery_detail_id
FROM mtl_txn_request_lines mol
WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
AND MOL.CROSSDOCK_TYPE = 1 --RESERVATION COULD BE FOR WIP ALSO???
AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
AND Nvl(mol.lot_number,'&^+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&^+')
AND mol.line_status = 7
AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mol.header_id)
)) OR
(mr.demand_source_line_detail IS NULL
AND exists (SELECT mol.backorder_delivery_detail_id
FROM mtl_txn_request_lines mol
WHERE mol.organization_id = p_cas_mol_rec_tb(1).organization_id
AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
AND mol.backorder_delivery_detail_id IS NULL
AND nvl(mol.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
AND Nvl(mol.lot_number,'&#+') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&#+')
AND mol.line_status = 7
AND (mol.quantity-Nvl(mol.quantity_delivered,0))>0
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mol.header_id)
)
)
)
ORDER BY NVL(MR.DEMAND_SHIP_DATE, REQUIREMENT_DATE)) LOOP
EXIT WHEN l_remaining_prim_qty <= 0;
SELECT line_id
INTO l_mo_line_id
FROM mtl_txn_request_lines mol
WHERE backorder_delivery_detail_id = l_rsv_results.demand_source_line_detail
AND mol.organization_id = p_cas_mol_rec_tb(1).organization_id
AND mol.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND NVL(mol.revision, '@@@') = NVL(p_cas_mol_rec_tb(1).item_revision, '@@@')
AND (NVL(mol.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id , -999)
or p_cas_mol_rec_tb(1).project_id is null) -- Bug 6618890
AND (NVL(mol.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
or p_cas_mol_rec_tb(1).task_id is null) -- Bug 6618890
AND mol.crossdock_type = 1
AND NVL(mol.lpn_id, -999) = NVL(p_cas_mol_rec_tb(1).lpn_id, -999)
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mol.header_id);
print_debug('Calling update_wdd...',l_module_name,4);
update_wdd
(x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_wdd_id => l_mo_split_tb(1).wdd_id
,p_released_status => 'R'
,p_mol_id => NULL
);
print_debug('Returned from update_wdd',l_module_name,4);
print_debug('Calling delete_reservation...',l_module_name,4);
delete_reservation
(p_rsv_rec => l_rsv_query_rec
,x_return_status => l_return_status
);
print_debug('Returned from delete_reservation',l_module_name,4);
print_debug('Calling update_wdd...',l_module_name,4);
update_wdd
(x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_wdd_id => l_rsv_results.demand_source_line_detail
,p_released_status => 'R'
,p_mol_id => NULL
);
print_debug('Returned from update_wdd',l_module_name,4);
UPDATE mtl_txn_request_lines
SET quantity = Nvl(quantity_delivered,0)
, primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
, quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
-- OPMConvergence
, secondary_quantity = Nvl(secondary_quantity_delivered,0)
, secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
-- OPMConvergence
, line_status = 5
, wms_process_flag = 1
WHERE line_id = l_tmp_line_id;
l_rsv_update_rec.primary_reservation_quantity := l_remaining_prim_qty;
l_rsv_update_rec.reservation_quantity := inv_rcv_cache.convert_qty(p_cas_mol_rec_tb(1).inventory_item_id
,l_remaining_prim_qty
,l_rsv_results.primary_uom_code
,l_rsv_results.reservation_uom_code);
SELECT rsh.receipt_source_code
INTO l_receipt_source_code
FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id;
print_debug('l_rsv_update_rec.primary_reservation_quantity = '||l_rsv_update_rec.primary_reservation_quantity ,l_module_name,4);
print_debug('l_rsv_update_rec.reservation_quantity = '||l_rsv_update_rec.reservation_quantity ,l_module_name,4);
l_rsv_update_rec.supply_source_type_id := g_source_type_asn;
l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
l_rsv_update_rec.supply_source_line_detail := p_cas_mol_rec_tb(1).shipment_line_id;
l_rsv_update_rec.supply_source_type_id := inv_reservation_global.g_source_type_po;
l_rsv_update_rec.supply_source_header_id := p_cas_mol_rec_tb(1).po_header_id;
l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).po_line_location_id;
l_rsv_update_rec.supply_source_line_detail := NULL;
l_rsv_update_rec.supply_source_type_id :=
inv_reservation_global.g_source_type_internal_req;
l_rsv_update_rec.supply_source_header_id := l_requisition_header_id;
l_rsv_update_rec.supply_source_line_id := p_cas_mol_rec_tb(1).requisition_line_id;
l_rsv_update_rec.supply_source_line_detail := NULL;
SELECT shipment_header_id
INTO l_shipment_header_id
FROM rcv_shipment_lines
WHERE shipment_line_id = p_cas_mol_rec_tb(1).shipment_line_id
FOR UPDATE NOWAIT;
l_rsv_update_rec.supply_source_type_id := g_source_type_in_transit;
l_rsv_update_rec.supply_source_header_id := l_shipment_header_id;
l_rsv_update_rec.supply_source_line_id := l_shipment_header_id;
l_rsv_update_rec.supply_source_line_detail := NULL;
,p_to_rsv_rec => l_rsv_update_rec
,x_new_rsv_id => l_new_rsv_id
,x_return_status => l_return_status);
FOR l_mol_rec IN (SELECT mtrl.line_id
, mtrl.primary_quantity
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_status = 7
AND (mtrl.quantity-Nvl(mtrl.quantity_delivered,0)) > 0
-- AND mtrl.backorder_delivery_detail_id IS NULL --Bug#6040524
AND mtrl.organization_id = p_cas_mol_rec_tb(1).organization_id
-- Bug 4508608
-- hadling of non lpn cases are done properly
-- AND mtrl.lpn_id = p_cas_mol_rec_tb(1).lpn_id
AND nvl(mtrl.lpn_id, -999) = nvl(p_cas_mol_rec_tb(1).lpn_id, -999)
AND mtrl.inventory_item_id = p_cas_mol_rec_tb(1).inventory_item_id
AND Nvl(mtrl.revision,'%^$') = Nvl(p_cas_mol_rec_tb(1).item_revision,'%^$')
AND Nvl(mtrl.inspection_status,-1) = Nvl(p_cas_mol_rec_tb(1).inspection_status,-1)
AND (NVL(mtrl.project_id, -999) = NVL(p_cas_mol_rec_tb(1).project_id, -999)
OR p_cas_mol_rec_tb(1).project_id IS NULL) -- Bug 6618890
AND (NVL(mtrl.task_id, -999) = NVL(p_cas_mol_rec_tb(1).task_id, -999)
OR p_cas_mol_rec_tb(1).task_id IS NULL) -- Bug 6618890
AND Nvl(mtrl.lot_number,'&*_') = Nvl(p_cas_mol_rec_tb(1).lot_number,'&*_')
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id)
)
LOOP
IF (l_debug = 1) THEN
print_debug('MOL found: '|| l_mol_rec.line_id||' QTY: '||l_mol_rec.primary_quantity,l_module_name,4);
UPDATE mtl_txn_request_lines
SET quantity = Nvl(quantity_delivered,0)
, primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
, quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
-- OPMConvergence
, secondary_quantity = Nvl(secondary_quantity_delivered,0)
, secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
-- OPMConvergence
, line_status = 5
, wms_process_flag = 1
WHERE line_id = l_tmp_line_id;
SELECT DISTINCT mtrl.line_id
, Decode(p_uom_code,mtrl.uom_code,1,2) UOM_ORDERING
, Decode(mmtt.transaction_source_type_id||'#'||mmtt.transaction_action_id,'1#27',1,
'7#12',1,'12#27',1,'13#12',1,'4#27',1,null) transaction_temp_id
, mtrl.wms_process_flag
, (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) quantity
, mtrl.primary_quantity
, mtrl.uom_code
, mtrl.lpn_id
, mtrl.inventory_item_id
, mtrl.lot_number
-- OPMConvergence
, (mtrl.secondary_quantity - Nvl(mtrl.secondary_quantity_delivered, 0)) secondary_quantity_2
, mtrl.secondary_quantity
, mtrl.secondary_uom_code
-- OPMConvergence
, mtrl.crossdock_type
, mtrl.backorder_delivery_detail_id
, mmtt.wip_supply_type
, mtrl.reference
, mtrl.reference_type_code
, mtrl.reference_id
FROM mtl_txn_request_lines mtrl
, mtl_material_transactions_temp mmtt
WHERE mtrl.organization_id = p_org_id
AND nvl(mtrl.from_subinventory_code,'@$#_') = nvl(p_from_sub,'@$#_')
AND nvl(mtrl.from_locator_id,-1) = nvl(p_from_locator_id,-1)
AND (nvl(mtrl.project_id,-1) = nvl(p_project_id,-1)
or p_project_id is null) -- Bug 6618890
AND (nvl(mtrl.task_id,-1) = nvl(p_task_id,-1)
or p_task_id is null) -- Bug 6618890
AND Nvl(inspection_status,-1) = Nvl(p_inspection_status,-1)
AND mtrl.inventory_item_id = p_item
AND Nvl(mtrl.revision, Nvl(p_rev, '@@@@')) = Nvl(p_rev, '@@@@')
AND Nvl(mtrl.lpn_id, -1) = Nvl(p_lpn, -1)
AND Nvl(mtrl.lot_number, Nvl(p_lot,'@$#_')) = Nvl(p_lot, '@$#_')
AND (mtrl.quantity - Nvl(mtrl.quantity_delivered, 0)) > 0
AND mmtt.move_order_line_id (+) = mtrl.line_id
AND mmtt.organization_id (+) = mtrl.organization_id
AND exists (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrh.header_id = mtrl.header_id)
--only pick up lines that are NOT loaded
AND (mmtt.transaction_temp_id IS NULL
OR
(mmtt.transaction_temp_id IS NOT NULL
--Bug 5231114:Added the condition on transaction_source_type_id and
--transaction_action_id for the following combinations:13/12 and 4/27.
AND ((mmtt.transaction_source_type_id = 1 AND mmtt.transaction_action_id = 27)
OR (mmtt.transaction_source_type_id = 7 AND mmtt.transaction_action_id = 12)
OR (mmtt.transaction_source_type_id = 12 AND mmtt.transaction_action_id = 27)
OR (mmtt.transaction_source_type_id = 13 AND mmtt.transaction_action_id = 12)
OR (mmtt.transaction_source_type_id = 4 AND mmtt.transaction_action_id = 27))
AND NOT exists (SELECT 1
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4) -- dispached or loaded
AND wdt.task_type = 2 -- putaway
)
)
)
ORDER BY 2 DESC, Nvl(transaction_temp_id, -1) ASC ;
SELECT '1'
INTO l_dummy
FROM dual
WHERE exists
(SELECT 1
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_mol_rec.line_id
AND ((transaction_source_type_id = 1 AND transaction_action_id = 27)
OR (transaction_source_type_id = 7 AND transaction_action_id = 12)
OR (transaction_source_type_id = 12 AND transaction_action_id = 27))
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status IN (3, 4) -- dispached or loaded
AND wdt.task_type = 2 -- putaway
);
--update the mol
UPDATE mtl_txn_request_lines
SET quantity = Nvl(quantity_delivered,0)
, primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
, quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
-- OPMConvergence
, secondary_quantity = Nvl(secondary_quantity_delivered,0)
, secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
-- OPMConvergence
, line_status = 5
, wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;
-- Call split_mo and then update the new line to quantity
-- = 0 and then
-- Call cancel operation plan for new line
IF (l_debug = 1) THEN
print_debug('CALLING SPLIT_MO:'||l_remaining_primary_quantity,l_module_name,4);
--update the new line for return
UPDATE mtl_txn_request_lines
SET quantity = Nvl(quantity_delivered,0)
, primary_quantity = ((primary_quantity*Nvl(quantity_delivered,0))/quantity)
, quantity_detailed = Decode(quantity_detailed,NULL,quantity_detailed,quantity_delivered)
-- OPMConvergence
, secondary_quantity = Nvl(secondary_quantity_delivered,0)
, secondary_quantity_detailed = Decode(secondary_quantity_detailed,NULL,secondary_quantity_detailed,secondary_quantity_delivered)
-- OPMConvergence
, line_status = 5
, wms_process_flag = 1
WHERE line_id = l_mo_split_tb(1).line_id;
--update the old line for wms process flag
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
WHERE line_id = l_mol_rec.line_id;