[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rt.organization_id
, rt.po_line_location_id
, rt.shipment_line_id
, rt.oe_order_line_id
, rt.lpn_id
, rt.transfer_lpn_id
, rsl.item_id
, rsl.item_revision
, rt.quantity
, rt.unit_of_measure
, rt.transaction_type
, rt.interface_transaction_id
, rt.destination_type_code
, rt.parent_transaction_id
--, rsl.shipment_line_id
--, poll.receiving_routing_id routing_id
, msi.lot_control_code
, DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
, rsl.from_organization_id
, rsl.asn_line_flag
FROM rcv_shipment_lines rsl
, mtl_system_items msi
, rcv_transactions rt
WHERE rt.group_id = p_group_id
AND (rt.transaction_type = 'CORRECT'
-- return to receiving is also created for a rtv/rtc txn.
-- from inventory. But for that we dont want to create
-- a move order so should eliminate those txns.
-- Those txns. will not have a transfer_lpn_id stamped
-- but the pure return_to_receiving txns. will have
-- destination_type_code = 'INVENTORY' unlike the pure
-- ones which will have destination_type_code = 'RECEIVING'
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND rt.destination_type_code = 'RECEIVING'))
AND rt.user_entered_flag = 'Y'
AND rsl.shipment_line_id = rt.shipment_line_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rt.organization_id;
SELECT rsl.item_id
, rt.po_line_location_id
, rt.shipment_line_id
, rt.oe_order_line_id
, rt.quantity rt_quantity
, rt.transaction_id
, rt.interface_transaction_id
, rt.lpn_id
, rt.transfer_lpn_id
, rt.primary_unit_of_measure
, rt.unit_of_measure
, rt.organization_id
, mtlt.lot_number
, mtlt.transaction_quantity
, rt.transaction_type
, rt.parent_transaction_id
, rsl.asn_line_flag
, DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
FROM mtl_transaction_lots_temp mtlt
, rcv_shipment_lines rsl
, rcv_transactions rt
, mtl_system_items msi
WHERE rt.group_id = p_group_id
AND (mtlt.transaction_temp_id (+) = rt.interface_transaction_id
-- Since mtlt is deleted for a correction record for a
-- deliver transaction, that record should be selected
-- from the union. So eliminating the selection of that
-- record from this part of the union.
AND NOT (rt.quantity > 0
AND rt.transaction_type = 'CORRECT'
AND msi.lot_control_code = 2
AND exists (SELECT 1
FROM rcv_transactions rt1
WHERE rt1.transaction_id = rt.parent_transaction_id
AND rt1.transaction_type = 'DELIVER')))
AND (rt.transaction_type = 'CORRECT'
-- select the return_to_receiving txn created for the
-- rtv/rtc transaction from inventory as for this all we
-- need to do is update the process_flag and not update any
-- mol since rtv/rtc from inventory does not effect mol
-- but for that the process_flag on mol was updated to 2.
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND rt.destination_type_code = 'INVENTORY')
OR (rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO CUSTOMER')
-- to eliminate the row being selected for a rtv
-- from inventory as for those we dont need to update
-- the move order line.
AND NOT exists (SELECT 1
FROM rcv_transactions rt2
WHERE rt2.interface_transaction_id = rt.interface_transaction_id
AND rt2.transaction_type = 'RETURN TO RECEIVING'
AND rt2.group_id = p_group_id)))
AND rt.user_entered_flag = 'Y'
AND rsl.shipment_line_id = rt.shipment_line_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rt.organization_id
UNION ALL
SELECT rsl.item_id
, rt.po_line_location_id
, rt.shipment_line_id
, rt.oe_order_line_id
, rt.quantity rt_quantity
, rt.transaction_id
, rt.interface_transaction_id
, rt.lpn_id
, rt.transfer_lpn_id
, rt.primary_unit_of_measure
, rt.unit_of_measure
, rt.organization_id
, mtln.lot_number
, mtln.transaction_quantity
, rt.transaction_type
, rt.parent_transaction_id
, rsl.asn_line_flag
, DECODE(MSI.RETURN_INSPECTION_REQUIREMENT,1,'Y','N') INSPECTION_REQUIRED_FLAG
FROM mtl_material_transactions mmt
, mtl_transaction_lot_numbers mtln
, rcv_shipment_lines rsl
, rcv_transactions rt
, mtl_system_items msi
WHERE rt.group_id = p_group_id
AND mmt.rcv_transaction_id = rt.transaction_id
AND mmt.transaction_id = mtln.transaction_id
-- should select in this part of the union only the cases which
-- have not been selected on top which are the ones for which the
-- row is deleted from mtlt.
AND rt.quantity > 0
AND rt.transaction_type = 'CORRECT'
AND msi.lot_control_code = 2
AND exists (SELECT 1
FROM rcv_transactions rt1
WHERE rt1.transaction_id = rt.parent_transaction_id
AND rt1.transaction_type = 'DELIVER')
AND rt.user_entered_flag = 'Y'
AND rsl.shipment_line_id = rt.shipment_line_id
AND msi.inventory_item_id = rsl.item_id
AND msi.organization_id = rt.organization_id;
CURSOR c_update_mo(l_transaction_id IN NUMBER
, l_item_id IN NUMBER
, l_lot_number in VARCHAR2
, v_reference IN VARCHAR2
, v_reference_id IN NUMBER
, v_lpn_id IN NUMBER
, v_inspection_status IN NUMBER
, v_organization_id IN NUMBER)
IS
SELECT mol.header_id
, mol.line_id
, mol.quantity mol_quantity
, nvl(mol.quantity_delivered,0) mol_quantity_delivered
, (mol.quantity - nvl(mol.quantity_delivered,0)) mol_available_quantity
, mol.lot_number
, mol.uom_code mol_uom_code
, mol.reference
, mol.lpn_id
, mol.inventory_item_id item_id
, rt.quantity rt_quantity
, rt.primary_unit_of_measure
, rt.unit_of_measure
, rt.organization_id
FROM mtl_txn_request_lines mol, rcv_transactions rt
WHERE rt.transaction_id = l_transaction_id
AND mol.organization_id = v_organization_id
AND rt.organization_id = v_organization_id
AND mol.reference_id = v_reference_id
AND mol.reference = v_reference
AND mol.inventory_item_id = l_item_id
AND mol.lpn_id = v_lpn_id
AND Nvl(mol.inspection_status,-1) = Nvl(v_inspection_status,-1)
AND nvl(mol.lot_number,'@@@') = nvl(l_lot_number,'@@@')
AND nvl(mol.quantity,0) - nvl(mol.quantity_delivered,0) > 0
ORDER BY nvl(mol.quantity,0) - nvl(mol.quantity_delivered,0) DESC;
l_update_lpn NUMBER;
SELECT transaction_type
INTO l_rtr_parent_txn_type
FROM rcv_transactions
WHERE transaction_id = i.parent_transaction_id;
SELECT transaction_type into l_parent_transaction_type
FROM rcv_transactions
WHERE transaction_id = i.parent_transaction_id;
l_lpn_id := NULL; -- MO is updated
l_lpn_id := NULL; -- MO is updated
l_lpn_id := NULL; -- MO is updated
SELECT receiving_routing_id
INTO l_routing_id
FROM po_line_locations_all
WHERE line_location_id = i.po_line_location_id;
SELECT Nvl(receiving_routing_id,1)
INTO l_routing_id
FROM rcv_parameters
WHERE organization_id = i.organization_id;
SELECT routing_header_id
INTO l_routing_id
FROM rcv_shipment_lines
WHERE shipment_line_id = i.shipment_line_id;
SELECT transaction_type
INTO l_grand_parent_txn_type
FROM rcv_transactions rt
WHERE transaction_id = (SELECT rt2.parent_transaction_id
FROM rcv_transactions rt2
WHERE rt2.transaction_id = i.parent_transaction_id);
SELECT uom_code INTO l_uom_code FROM mtl_item_uoms_view
WHERE organization_id = i.organization_id
AND unit_of_measure = i.unit_of_measure
AND inventory_item_id = i.item_id;
-- Donot need to do this since it will get updated in
-- the end anyway.
--UPDATE mtl_txn_request_lines
--SET wms_process_flag = 1
--WHERE txn_source_id = i.interface_transaction_id;
l_update_lpn := l_lpn_id;
l_update_lpn := i.lpn_id;
print_debug('Updating MOLs for:'||l_update_lpn);
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
, txn_source_line_detail_id = NULL
WHERE lpn_id = l_update_lpn
AND txn_source_line_detail_id = i.interface_transaction_id;
SELECT transaction_type INTO l_rtv_parent_txn_type
FROM rcv_transactions
WHERE transaction_id = i.parent_transaction_id;
-- of a rtv/rtc from inventory we dont need to update or create
-- any mol but we do need to update the process_flag as that
-- was updated.
l_lpn_id := NULL;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
, txn_source_line_detail_id = NULL
WHERE lpn_id = i.lpn_id
AND txn_source_line_detail_id = i.interface_transaction_id;
SELECT transaction_type into l_parent_transaction_type
FROM rcv_transactions
WHERE transaction_id = i.parent_transaction_id;
-- but we still need to update the old mo.
IF i.rt_quantity < 0 THEN
l_lpn_id := i.transfer_lpn_id;
print_debug('lpn_id being used to update a mo:'||l_lpn_id);
-- For MOL update of ACCEPT/REJECT transaction we have to select
-- MOL with proper status.
IF (l_parent_transaction_type IN ('ACCEPT', 'REJECT')
OR l_rtv_parent_txn_type IN ('ACCEPT', 'REJECT')) THEN
IF (l_parent_transaction_type IN ('ACCEPT','REJECT')) THEN
IF i.rt_quantity > 0 THEN
IF (l_debug = 1) THEN
print_debug('+ve correction for inspect for mol update');
SELECT receiving_routing_id
INTO l_routing_id
FROM po_line_locations_all
WHERE line_location_id = i.po_line_location_id;
SELECT Nvl(receiving_routing_id,1)
INTO l_routing_id
FROM rcv_parameters
WHERE organization_id = i.organization_id;
SELECT routing_header_id
INTO l_routing_id
FROM rcv_shipment_lines
WHERE shipment_line_id = i.shipment_line_id;
SELECT transaction_type
INTO l_grand_parent_txn_type
FROM rcv_transactions rt
WHERE transaction_id = (SELECT rt2.parent_transaction_id
FROM rcv_transactions rt2
WHERE rt2.transaction_id = i.parent_transaction_id);
FOR j IN c_update_mo(i.transaction_id, i.item_id, i.lot_number,
l_reference, l_reference_id, l_lpn_id,
l_inspection_status, i.organization_id) LOOP
IF (l_debug = 1) THEN
print_debug('Opened update MOL for line_id:'||j.line_id);
SELECT unit_of_measure INTO l_mol_unit_of_measure
FROM mtl_item_uoms_view
WHERE organization_id = i.organization_id
AND uom_code = j.mol_uom_code
AND inventory_item_id = i.item_id;
UPDATE mtl_txn_request_lines
SET quantity = quantity - l_rt_qty_in_mol_uom
, primary_quantity = primary_quantity - l_rt_qty_in_primary_uom
WHERE header_id = j.header_id
AND nvl(lot_number,'@@@') = nvl(j.lot_number,'@@@')
AND line_id = j.line_id;
print_debug('Before update of mtrl within IF');
UPDATE mtl_txn_request_lines
SET line_status=5
WHERE header_id = j.header_id
AND line_id = j.line_id
AND ( nvl(quantity,0) = 0 OR
nvl(quantity,0)=nvl(quantity_delivered,0)
) ;
print_debug('After update of mtrl within IF');
print_debug('updated mol:'||j.line_id||' and exiting');
UPDATE mtl_txn_request_lines
SET quantity = quantity - abs(j.mol_available_quantity) --l_rt_qty_in_mol_uom
, primary_quantity = primary_quantity - l_mol_qty_in_primary_uom
WHERE header_id = j.header_id
AND nvl(lot_number,'@@@') = nvl(j.lot_number,'@@@')
AND line_id = j.line_id;
print_debug(' Before update of mtrl within ELSE');
UPDATE mtl_txn_request_lines
SET line_status=5
WHERE header_id = j.header_id
AND line_id = j.line_id
AND ( nvl(quantity,0) = 0 OR
nvl(quantity,0)=nvl(quantity_delivered,0)
);
print_debug(' After update of mtrl within ELSE');
print_debug('updated mol:'||j.line_id);
END LOOP; -- c_update_mo
print_debug('finished finding mol for update');
UPDATE mtl_txn_request_lines
SET wms_process_flag = 1
, txn_source_line_detail_id = NULL
WHERE lpn_id = l_lpn_id
AND txn_source_line_detail_id = i.interface_transaction_id;
-- which the move order is being updated after deleting the
-- existing suggestions.
BEGIN
SELECT pregen_putaway_tasks_flag
INTO l_pregen_putaway_tasks_flag
FROM mtl_parameters
WHERE organization_id = i.organization_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id;
SELECT mtl_transactions_enabled_flag
INTO l_transactable_flag
FROM mtl_system_items_b
WHERE inventory_item_id = p_content_item_id
AND organization_id = p_organization_id;
SELECT wlpnc.organization_id
, wlpn.subinventory_code subinventory
, wlpn.locator_id
, rt.lpn_id lpn_id
, rt.transfer_lpn_id
, wlpnc.inventory_item_id
, wlpnc.revision
, wlpnc.lot_number
, to_char(null) serial_number
, wlpnc.quantity
, wlpnc.uom_code
, rt.transaction_type
, rt.interface_transaction_id
, wlpnc.COST_GROUP_ID cg_id
, rt.destination_type_code
, rt.quantity rt_quantity
, rt.parent_transaction_id
FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlpnc, rcv_transactions rt
WHERE rt.group_id = p_group_id
AND (((( rt.transaction_type = 'RETURN TO VENDOR'
AND rt.lpn_id IS NOT NULL -- 3603808
)
OR
( rt.transaction_type = 'RETURN TO CUSTOMER'
-- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
AND rt.lpn_id IS NOT NULL
))
-- to eliminate the row being selected for a rtv
-- from inventory as pack unpack for that is already
-- taken care of in inventory tm.
AND NOT exists (SELECT 1
FROM rcv_transactions rt2
WHERE rt2.interface_transaction_id = rt.interface_transaction_id
AND rt2.transaction_type = 'RETURN TO RECEIVING'
AND rt2.group_id = p_group_id))
OR (rt.transaction_type = 'CORRECT')
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND rt.transfer_lpn_id IS NOT NULL
AND rt.lpn_id IS NOT NULL))
AND rt.user_entered_flag = 'Y'
AND wlpnc.source_name = rt.transaction_type
AND wlpnc.source_header_id = rt.interface_transaction_id
AND nvl(wlpnc.serial_summary_entry,2) <> 1
AND wlpn.lpn_id = wlpnc.parent_lpn_id
UNION ALL
SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
msn.inventory_item_id, msn.revision, msn.lot_number,
msn.serial_number, to_number(null) quantity, wlpnc.uom_code uom_code, rt.transaction_type,
rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt
WHERE msn.last_txn_source_name = rt.transaction_type
AND msn.last_txn_source_id = rt.interface_transaction_id
AND rt.group_id = p_group_id
AND (((( rt.transaction_type = 'RETURN TO VENDOR' -- 3603808
AND rt.lpn_id IS NOT NULL
)
OR
( rt.transaction_type = 'RETURN TO CUSTOMER'
-- AND rt.transfer_lpn_id IS NOT NULL fix for 4389811
AND rt.lpn_id IS NOT NULL
))
AND NOT exists (SELECT 1
FROM rcv_transactions rt2
WHERE rt2.interface_transaction_id = rt.interface_transaction_id
AND rt2.transaction_type = 'RETURN TO RECEIVING'
AND rt2.group_id = p_group_id))
OR (rt.transaction_type = 'CORRECT') OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND rt.transfer_lpn_id IS NOT NULL
AND rt.lpn_id IS NOT NULL))
AND rt.user_entered_flag = 'Y'
AND wlpnc.parent_lpn_id = msn.lpn_id
AND wlpnc.inventory_item_id = msn.inventory_item_id;
SELECT transaction_type, interface_transaction_id, item_id
FROM rcv_transactions_interface rti
WHERE rti.group_id = p_group_id
AND rti.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER','RETURN TO RECEIVING','CORRECT');
SELECT wlpnc.organization_id
, rti.lpn_id lpn_id
, rti.transfer_lpn_id
, wlpnc.inventory_item_id
, wlpnc.revision
, wlpnc.lot_number
, to_char(null) serial_number
, wlpnc.quantity
, wlpnc.uom_code
, rti.transaction_type
, rti.interface_transaction_id
, rti.destination_type_code
, rti.quantity rti_quantity
, rti.parent_transaction_id
FROM wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
WHERE rti.group_id = p_group_id
AND rti.transaction_type = 'CORRECT'
AND rt.transaction_id = rti.parent_transaction_id
AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
(rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
AND wlpnc.source_name = rti.transaction_type
AND wlpnc.source_header_id = rti.interface_transaction_id
AND nvl(wlpnc.serial_summary_entry,2) <> 1
UNION ALL
SELECT msn.current_organization_id organization_id
, rti.lpn_id
, rti.transfer_lpn_id
, msn.inventory_item_id
, msn.revision
, msn.lot_number
, msn.serial_number
, to_number(null) quantity
, wlpnc.uom_code
, rti.transaction_type
, rti.interface_transaction_id
, rti.destination_type_code
, rti.quantity rti_quantity
, rti.parent_transaction_id
FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
WHERE msn.last_txn_source_name = rti.transaction_type
AND msn.last_txn_source_id = rti.interface_transaction_id
AND rti.group_id = p_group_id
AND rti.transaction_type = 'CORRECT'
AND rt.transaction_id = rti.parent_transaction_id
AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
(rt.transaction_type = 'RECEIVE' AND rti.quantity > 0))
AND wlpnc.parent_lpn_id = msn.lpn_id
AND wlpnc.inventory_item_id = msn.inventory_item_id;
SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
msn.inventory_item_id, msn.revision, msn.lot_number,
msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.group_id = p_group_id
AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
AND msn.current_subinventory_code = rt.from_subinventory
AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
AND msn.current_organization_id = rt.organization_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND msn.inventory_item_id = rsl.item_id
AND rt.user_entered_flag = 'Y'
AND msn.current_status = 4
AND exists (SELECT '1' FROM rcv_transactions rt2
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'DELIVER');
SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
msn.inventory_item_id, msn.revision, msn.lot_number,
msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.transaction_date >= (Sysdate - 1)
AND rt.lpn_group_id = p_group_id
AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
AND msn.current_subinventory_code = rt.from_subinventory
AND Nvl(msn.current_locator_id,-1) = Nvl(rt.from_locator_id,-1)
AND msn.current_organization_id = rt.organization_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND msn.inventory_item_id = rsl.item_id
AND rt.user_entered_flag = 'Y'
AND msn.current_status = 4
AND exists (SELECT '1' FROM rcv_transactions rt2
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'DELIVER');
SELECT msn.current_organization_id organization_id, msn.current_subinventory_code subinventory,
msn.current_locator_id locator_id, rt.lpn_id, rt.transfer_lpn_id,
msn.inventory_item_id, msn.revision, msn.lot_number,
msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.group_id = p_group_id
AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
OR (rt.transaction_type = 'RETURN TO RECEIVING'
AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
AND msn.current_subinventory_code = rt.subinventory
AND Nvl(msn.current_locator_id,-1) = Nvl(rt.locator_id,-1)
AND msn.current_organization_id = rt.organization_id
AND rsl.shipment_header_id = rt.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND msn.inventory_item_id = rsl.item_id
AND rt.user_entered_flag = 'Y'
AND msn.current_status = 4
AND exists (SELECT '1' FROM rcv_transactions rt2
WHERE rt2.transaction_id = rt.parent_transaction_id
AND rt2.transaction_type = 'DELIVER');
l_res_rec_to_delete INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
SELECT rt.source_document_code,
rt.organization_id,
rsl.item_id,
rt.subinventory,
rt.locator_id,
rt.from_subinventory,
rt.from_locator_id,
rt.lpn_id,
rt.quantity
FROM rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.group_id = p_group_id
AND p_txn_mode <> 'LPN_GROUP'
AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rt.shipment_line_id = rsl.shipment_line_id;
SELECT rt.source_document_code,
rt.organization_id,
rsl.item_id,
rt.subinventory,
rt.locator_id,
rt.from_subinventory,
rt.from_locator_id,
rt.lpn_id,
rt.quantity
FROM rcv_transactions rt, rcv_shipment_lines rsl
WHERE rt.transaction_date >= (SYSDATE-1)
AND rt.lpn_group_id = p_group_id
AND p_txn_mode = 'LPN_GROUP'
AND rt.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rt.shipment_line_id = rsl.shipment_line_id;
SELECT interface_transaction_id
FROM rcv_transactions
WHERE group_id = p_group_id;
SELECT transaction_type, routing_header_id
INTO l_parent_transaction_type, l_routing_header_id
FROM rcv_transactions
WHERE transaction_id = i.parent_transaction_id;
-- Update the context for the pack lpn to 'resides in
-- receiving' as packunpack api may have changed it to
-- 'Defined but not used'
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
WHERE lpn_id = l_pack_lpn;
-- update the lpn_context as while unpacking, the
-- packunpack api might have changed the context
-- to 'Defined But not used'
IF (l_parent_transaction_type = 'RECEIVE') THEN
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
WHERE lpn_id = l_lpn_id;
UPDATE mtl_serial_numbers
SET current_status = l_status
, inspection_status = l_insp_status
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = i.serial_number
AND inventory_item_id = i.inventory_item_id;
print_debug('Updated sn for correction of rtv and receive to status:'||l_status);
UPDATE mtl_serial_numbers
SET inspection_status = l_status
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = i.serial_number
AND inventory_item_id = i.inventory_item_id;
print_debug('Updated inspection status of sn for correction of accept/reject');
UPDATE mtl_serial_numbers
SET current_status = l_status
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = i.serial_number
AND inventory_item_id = i.inventory_item_id;
UPDATE mtl_serial_numbers
SET current_status = l_status
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = i.serial_number
AND inventory_item_id = i.inventory_item_id;
--update the reservation. If the entire quantity is to be returned, then
--clear the reservation
l_res_rec_to_delete := l_mtl_reservation_tbl(l_counter);
INV_RESERVATION_PUB.DELETE_RESERVATION(
p_api_version_number => 1.0,
p_init_msg_lst => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rsv_rec => l_res_rec_to_delete,
p_serial_number => l_dummy_sn
);
print_debug('TXN_COMPLETE: Deleted the reservation record successfully');
END LOOP; --END delete all the reservation records
UPDATE mtl_serial_numbers
SET
current_status = 5
, current_subinventory_code = NULL
, current_locator_id = NULL
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = irec.serial_number
AND inventory_item_id = irec.inventory_item_id ;
print_debug('TXN_COMPLETE: Deliver Transaction. 1159.. updated serial... '||irec.serial_number);
UPDATE mtl_serial_numbers
SET
--current_status = 5
current_status = l_status
, current_subinventory_code = NULL
, current_locator_id = NULL
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = irec.serial_number
AND inventory_item_id = irec.inventory_item_id
AND exists (SELECT '1' FROM rcv_serials_supply rss
WHERE rss.serial_num = serial_number
AND rss.supply_type_code = 'RECEIVING');
print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
UPDATE mtl_serial_numbers
SET
--current_status = 5
current_status = l_status
, current_subinventory_code = NULL
, current_locator_id = NULL
, last_txn_source_name = NULL
, last_txn_source_id = NULL
, group_mark_id = NULL
, line_mark_id = NULL
, cost_group_id = NULL
WHERE serial_number = irec.serial_number
AND inventory_item_id = irec.inventory_item_id
AND exists (SELECT '1' FROM rcv_serials_supply rss
WHERE rss.serial_num = serial_number
AND rss.supply_type_code = 'RECEIVING');
print_debug('TXN_COMPLETE: Deliver Transaction... updated serial... '||irec.serial_number);
DELETE FROM MTL_SERIAL_NUMBERS_TEMP
WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
DELETE FROM MTL_TRANSACTION_LOTS_TEMP
WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
print_debug('txn_complete- failure: Update Contents/Serials that were marked, erasing Source_Name');
UPDATE wms_lpn_contents
SET source_name = NULL
WHERE source_name = i.transaction_type
AND source_header_id = i.interface_transaction_id;
-- Only update MSN if an item ID exists on the RTI record.
IF (i.item_id IS NOT NULL) THEN
UPDATE mtl_serial_numbers
SET last_txn_source_name = NULL,
current_status = nvl(previous_status,current_status),
lpn_txn_error_flag = 'Y'
WHERE last_txn_source_name = i.transaction_type
AND last_txn_source_id = i.interface_transaction_id
AND inventory_item_id = i.item_id;
SELECT '1' into v_dummy
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND current_organization_id = p_org_id
AND nvl(last_txn_source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rownum <= 1;
SELECT '1' INTO v_dummy
FROM wms_lpn_contents
WHERE nvl(serial_summary_entry,2) <> 1
AND parent_lpn_id = p_lpn_id
AND organization_id = p_org_id
AND nvl(source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rownum <= 1;
SELECT '1' into v_dummy
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND current_organization_id = p_org_id
AND nvl(last_txn_source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rownum <= 1;
SELECT '1' INTO v_dummy
FROM wms_lpn_contents
WHERE nvl(serial_summary_entry,2) <> 1
AND parent_lpn_id = p_lpn_id
AND ORGANIZATION_ID = p_org_id
AND nvl(source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
AND rownum <= 1;
** that are selected for return.
*/
PROCEDURE MARK_RETURNS (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_trx_interface_id IN NUMBER,
p_ret_transaction_type IN VARCHAR2,
p_lpn_id IN NUMBER,
p_item_id IN NUMBER,
p_item_revision IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_serial_controlled IN NUMBER,
p_lot_controlled IN NUMBER,
p_org_id IN NUMBER,
p_subinventory IN VARCHAR2,
p_locator_id IN NUMBER
) IS
-- Increased lot size to 80 Char - Mercy Thomas - B4625329
l_lot_number VARCHAR2(80);
l_lpn_update WMS_CONTAINER_PUB.LPN;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE organization_id = p_org_id
AND lpn_id = p_lpn_id;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id ;
open c_ref for SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
AND MSN.INVENTORY_ITEM_ID = p_item_id
AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER;
open c_ref for SELECT DISTINCT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
NULL TO_SERIAL_NUMBER, MTLT.transaction_quantity quantity,wlpnc.cost_group_id
FROM WMS_LPN_CONTENTS WLPNC, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
AND WLPNC.LOT_NUMBER = MTLT.LOT_NUMBER
AND WLPNC.PARENT_LPN_ID = p_lpn_id
AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER');
open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,
wms_lpn_contents wlpnc
WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
AND MSN.INVENTORY_ITEM_ID = p_item_id
AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
AND MSN.SERIAL_NUMBER <= MSNT.to_serial_number
AND msn.lpn_id = wlpnc.parent_lpn_id
AND wlpnc.parent_lpn_id = p_lpn_id
AND wlpnc.inventory_item_id = msn.inventory_item_id
AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
open c_ref for SELECT NULL , NULL , NULL , rti.quantity, wlpnc.cost_group_id
FROM WMS_LPN_CONTENTS WLPNC, RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id
AND WLPNC.PARENT_LPN_ID = p_lpn_id
AND WLPNC.INVENTORY_ITEM_ID = RTI.ITEM_ID
AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
AND rownum <= 1;
l_lpn_update.lpn_id := p_lpn_id ;
l_lpn_update.organization_id := p_org_id ;
l_lpn_update.lpn_context := l_lpn_context ;
, p_lpn => l_lpn_update
) ;
l_lpn_update := NULL;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
, txn_source_line_detail_id = p_rcv_trx_interface_id
WHERE lpn_id = p_lpn_id;
** that are selected for +ve correction into Receiving.
*/
PROCEDURE PACK_INTO_RECEIVING (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_trx_interface_id IN NUMBER,
p_ret_transaction_type IN VARCHAR2,
p_lpn_id IN NUMBER,
p_item_id IN NUMBER,
p_item_revision IN VARCHAR2,
p_quantity IN NUMBER,
p_uom IN VARCHAR2,
p_serial_controlled IN NUMBER,
p_lot_controlled IN NUMBER,
p_org_id IN NUMBER
) IS
-- Increased lot size to 80 Char - Mercy Thomas - B4625329
l_lot_number VARCHAR2(80);
open c_ref for SELECT mtlt.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
AND MSN.INVENTORY_ITEM_ID = p_item_id
AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
--AND MSN.LOT_NUMBER = MTLT.LOT_NUMBER
AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER;
open c_ref for SELECT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
NULL TO_SERIAL_NUMBER, MTLT.TRANSACTION_QUANTITY quantity
FROM MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id;
open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT
WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
AND MSN.INVENTORY_ITEM_ID = p_item_id
AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
AND MSN.SERIAL_NUMBER <= MSNT.TO_SERIAL_NUMBER
AND length(MSN.SERIAL_NUMBER) = length(MSNT.FM_SERIAL_NUMBER);
open c_ref for SELECT NULL , NULL , NULL , rti.quantity
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id;
/* Update the previous_status of serial to current_status before doing anything.
** This is needed so that current_status can be put back to previous_status
** if the txn fails. Bringing back the status is done in txn_complete
** if txn fails.
*/
IF l_from_serial_number IS NOT NULL THEN
UPDATE mtl_serial_numbers
SET previous_status = current_status
WHERE serial_number = l_from_serial_number
AND inventory_item_id = p_item_id;
UPDATE wms_license_plate_numbers
SET lpn_context = wms_container_pub.lpn_context_rcv
WHERE lpn_id = p_lpn_id;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
, txn_source_line_detail_id = p_rcv_trx_interface_id
WHERE lpn_id = p_lpn_id;
open c_ref for SELECT SOURCE_HEADER_ID
FROM WMS_LPN_CONTENTS WLPNC
WHERE WLPNC.ORGANIZATION_ID = p_org_id
AND WLPNC.PARENT_LPN_ID = p_lpn_id
AND NVL(SERIAL_SUMMARY_ENTRY,2) <> 1 -- Non Serial Contents Records(value=2)
AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING');
UPDATE RCV_TRANSACTIONS_INTERFACE
SET GROUP_ID = p_group_id,
PROCESSING_MODE_CODE = p_txn_proc_mode,
MOBILE_TXN = 'Y'
WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
open c_ref_ser for SELECT LAST_TXN_SOURCE_ID
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.LPN_ID = p_lpn_id
AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING');
UPDATE RCV_TRANSACTIONS_INTERFACE
SET GROUP_ID = p_group_id,
PROCESSING_MODE_CODE = p_txn_proc_mode,
MOBILE_TXN = 'Y'
WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
open c_ref for SELECT SOURCE_HEADER_ID
FROM WMS_LPN_CONTENTS WLPNC
WHERE WLPNC.ORGANIZATION_ID = p_org_id
AND WLPNC.PARENT_LPN_ID = p_lpn_id
AND WLPNC.INVENTORY_ITEM_ID = p_item_id
AND ((WLPNC.revision = p_item_revision AND p_item_revision IS NOT NULL) OR
(WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING');
open c_ref for SELECT LAST_TXN_SOURCE_ID
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.LPN_ID = p_lpn_id
AND MSN.INVENTORY_ITEM_ID = p_item_id
AND ((MSN.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
(MSN.REVISION IS NULL AND p_item_revision IS NULL))
AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING')
AND MSN.SERIAL_NUMBER = p_serial_code;
open c_ref for SELECT SOURCE_HEADER_ID
FROM WMS_LPN_CONTENTS WLPNC
WHERE WLPNC.ORGANIZATION_ID = p_org_id
AND WLPNC.PARENT_LPN_ID = p_lpn_id
AND WLPNC.INVENTORY_ITEM_ID = p_item_id
AND ((WLPNC.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
(WLPNC.REVISION IS NULL AND p_item_revision IS NULL))
AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
'RETURN TO CUSTOMER',
'RETURN TO RECEIVING')
AND WLPNC.LOT_NUMBER = p_lot_code;
UPDATE RCV_TRANSACTIONS_INTERFACE
SET GROUP_ID = p_group_id,
PROCESSING_MODE_CODE = p_txn_proc_mode,
MOBILE_TXN = 'Y'
WHERE INTERFACE_TRANSACTION_ID = l_rtiid;
UPDATE RCV_TRANSACTIONS_INTERFACE
SET TRANSFER_LPN_ID = p_to_lpn_id
WHERE INTERFACE_TRANSACTION_ID = l_rtiid
AND NVL(TRANSACTION_TYPE, '@@@') = 'RETURN TO RECEIVING';
SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL INTO l_groupid FROM DUAL;
select distinct wlpnc.license_plate_number
from wms_license_plate_numbers wlpnc, rcv_transactions_interface rti
where rti.lpn_id = p_lpn_id
and rti.item_id = p_item_id
and nvl(rti.item_revision, '@@@') = nvl(p_revision, '@@@')
and nvl(rti.transaction_type, '@@@') = 'RETURN TO RECEIVING'
and rti.transfer_lpn_id is not null
and wlpnc.lpn_id = rti.transfer_lpn_id
and wlpnc.organization_id = p_org_id;
SELECT lot_number, primary_quantity
FROM mtl_transaction_lots_temp
WHERE product_code = 'RCV'
AND product_transaction_id = p_intf_txn_id;
SELECT lot_number, primary_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_intf_txn_id;
SELECT uom_code
INTO l_uom_code
FROM mtl_item_uoms_view
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND unit_of_measure = p_unit_of_measure;
SELECT primary_uom_code, lot_control_code
INTO l_item_primary_uom, l_lot_control_code
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
--Update the reservation record with the new quantity
INV_RESERVATION_PUB.UPDATE_RESERVATION(
p_api_version_number => 1.0,
p_init_msg_lst => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_original_rsv_rec => l_upd_reservation_tbl(l_count),
p_to_rsv_rec => l_upd_reservation_record,
p_original_serial_number => l_dummy_sn,
p_to_serial_number => l_dummy_sn,
p_validation_flag => FND_API.G_TRUE);
print_debug('CREATE_RETURN_RESV:error in update_reservation');
print_debug('CREATE_RETURN_RESV:successfully updated a reservation record: ' ||sql%rowcount);