[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* inserting MTL_SERIAL_NUMBERS for the serial numbers in the LPN
*/
TYPE msn_attribute_rec_tp IS RECORD(
serial_number mtl_serial_numbers.serial_number%TYPE
, to_serial_number mtl_serial_numbers.serial_number%TYPE
, vendor_serial_number mtl_serial_numbers.vendor_serial_number%TYPE
, vendor_lot_number mtl_serial_numbers.vendor_lot_number%TYPE
, parent_serial_number mtl_serial_numbers.parent_serial_number%TYPE
, origination_date mtl_serial_numbers.origination_date%TYPE
, end_item_unit_number mtl_serial_numbers.end_item_unit_number%TYPE
, territory_code mtl_serial_numbers.territory_code%TYPE
, time_since_new mtl_serial_numbers.time_since_new%TYPE
, cycles_since_new mtl_serial_numbers.cycles_since_new%TYPE
, time_since_overhaul mtl_serial_numbers.time_since_overhaul%TYPE
, cycles_since_overhaul mtl_serial_numbers.cycles_since_overhaul%TYPE
, time_since_repair mtl_serial_numbers.time_since_repair%TYPE
, cycles_since_repair mtl_serial_numbers.cycles_since_repair%TYPE
, time_since_visit mtl_serial_numbers.time_since_visit%TYPE
, cycles_since_visit mtl_serial_numbers.cycles_since_visit%TYPE
, time_since_mark mtl_serial_numbers.time_since_mark%TYPE
, cycles_since_mark mtl_serial_numbers.cycles_since_mark%TYPE
, number_of_repairs mtl_serial_numbers.number_of_repairs%TYPE
, serial_attribute_category mtl_serial_numbers.serial_attribute_category%TYPE
, c_attribute1 mtl_serial_numbers.c_attribute1%TYPE
, c_attribute2 mtl_serial_numbers.c_attribute2%TYPE
, c_attribute3 mtl_serial_numbers.c_attribute3%TYPE
, c_attribute4 mtl_serial_numbers.c_attribute4%TYPE
, c_attribute5 mtl_serial_numbers.c_attribute5%TYPE
, c_attribute6 mtl_serial_numbers.c_attribute6%TYPE
, c_attribute7 mtl_serial_numbers.c_attribute7%TYPE
, c_attribute8 mtl_serial_numbers.c_attribute8%TYPE
, c_attribute9 mtl_serial_numbers.c_attribute9%TYPE
, c_attribute10 mtl_serial_numbers.c_attribute10%TYPE
, c_attribute11 mtl_serial_numbers.c_attribute11%TYPE
, c_attribute12 mtl_serial_numbers.c_attribute12%TYPE
, c_attribute13 mtl_serial_numbers.c_attribute13%TYPE
, c_attribute14 mtl_serial_numbers.c_attribute14%TYPE
, c_attribute15 mtl_serial_numbers.c_attribute15%TYPE
, c_attribute16 mtl_serial_numbers.c_attribute16%TYPE
, c_attribute17 mtl_serial_numbers.c_attribute17%TYPE
, c_attribute18 mtl_serial_numbers.c_attribute18%TYPE
, c_attribute19 mtl_serial_numbers.c_attribute19%TYPE
, c_attribute20 mtl_serial_numbers.c_attribute20%TYPE
, d_attribute1 mtl_serial_numbers.d_attribute1%TYPE
, d_attribute2 mtl_serial_numbers.d_attribute2%TYPE
, d_attribute3 mtl_serial_numbers.d_attribute3%TYPE
, d_attribute4 mtl_serial_numbers.d_attribute4%TYPE
, d_attribute5 mtl_serial_numbers.d_attribute5%TYPE
, d_attribute6 mtl_serial_numbers.d_attribute6%TYPE
, d_attribute7 mtl_serial_numbers.d_attribute7%TYPE
, d_attribute8 mtl_serial_numbers.d_attribute8%TYPE
, d_attribute9 mtl_serial_numbers.d_attribute9%TYPE
, d_attribute10 mtl_serial_numbers.d_attribute10%TYPE
, n_attribute1 mtl_serial_numbers.n_attribute1%TYPE
, n_attribute2 mtl_serial_numbers.n_attribute2%TYPE
, n_attribute3 mtl_serial_numbers.n_attribute3%TYPE
, n_attribute4 mtl_serial_numbers.n_attribute4%TYPE
, n_attribute5 mtl_serial_numbers.n_attribute5%TYPE
, n_attribute6 mtl_serial_numbers.n_attribute6%TYPE
, n_attribute7 mtl_serial_numbers.n_attribute7%TYPE
, n_attribute8 mtl_serial_numbers.n_attribute8%TYPE
, n_attribute9 mtl_serial_numbers.n_attribute9%TYPE
, n_attribute10 mtl_serial_numbers.n_attribute10%TYPE
);
SELECT MAX(line_number)
INTO l_line_num
FROM mtl_txn_request_lines
WHERE header_id = p_header_id;
l_trolin_tbl(l_order_count).last_updated_by := fnd_global.user_id;
l_trolin_tbl(l_order_count).last_update_date := SYSDATE;
l_trolin_tbl(l_order_count).last_update_login := fnd_global.login_id;
SELECT from_subinventory_code
, from_cost_group_id
, to_cost_group_id
INTO l1
, l2
, l3
FROM mtl_txn_request_lines
WHERE line_id = l_cg_line;
SELECT 1
INTO l_project_comingle
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn
AND organization_id = p_org_id
AND line_status <> inv_globals.g_to_status_closed
AND NVL(project_id, -1) <> NVL(p_project_id, -1)
AND NVL(task_id, -1) <> NVL(p_task_id, -1));
l_trohdr_rec.last_updated_by := fnd_global.user_id;
l_trohdr_rec.last_update_date := SYSDATE;
l_trohdr_rec.last_update_login := fnd_global.user_id;
l_trolin_tbl(l_order_count).last_updated_by := fnd_global.user_id;
l_trolin_tbl(l_order_count).last_update_date := SYSDATE;
l_trolin_tbl(l_order_count).last_updated_by := fnd_global.user_id;
l_trolin_tbl(l_order_count).last_update_date := SYSDATE;
l_trolin_tbl(l_order_count).last_update_login := fnd_global.login_id;
SELECT from_subinventory_code
, from_cost_group_id
, to_cost_group_id
INTO l1
, l2
, l3
FROM mtl_txn_request_lines
WHERE line_id = l_cg_line;
SELECT mtrl.line_id
, mtrl.reference_id
, mtrl.transaction_source_type_id
, mtrl.quantity_detailed
, mtrl.quantity
, mtrl.backorder_delivery_detail_id
, NVL(crossdock_type, 1)
, mtrl.to_subinventory_code
, mtrl.to_locator_id
, mtrl.reference_detail_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_org_id
AND mtrl.header_id = mtrh.header_id
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
AND mtrl.line_id = NVL(p_move_order_line_id, mtrl.line_id) -- added for ATF_J
--BUG 5194761
UNION
SELECT mtrl.line_id
, mtrl.reference_id
, mtrl.transaction_source_type_id
, mtrl.quantity_detailed
, mtrl.quantity
, mtrl.backorder_delivery_detail_id
, NVL(crossdock_type, 1)
, mtrl.to_subinventory_code
, mtrl.to_locator_id
, mtrl.reference_detail_id
FROM mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_org_id
AND mtrl.header_id = mtrh.header_id
AND mtrl.line_status <> inv_globals.g_to_status_closed
AND mtrh.move_order_type = inv_globals.g_move_order_put_away
AND p_move_order_line_id IS NOT NULL
AND mtrl.reference_detail_id = p_move_order_line_id;
SELECT mmtt.last_update_date
, mmtt.transaction_temp_id
, mmtt.locator_id mmtt_loc_id
, mol.to_locator_id mol_loc_id
, mol.backorder_delivery_detail_id
, mmtt.operation_plan_id
, mol.inspection_status
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
WHERE mmtt.lpn_id = l_lpn_id
-- Added following line for ATF_J
AND mmtt.move_order_line_id = NVL(p_move_order_line_id, mmtt.move_order_line_id)
AND mmtt.organization_id = p_org_id
-- Added for ATF_J to make sure dummy packing MMTT lines are not selected
AND mmtt.transaction_action_id NOT IN (50, 51, 52)
AND mmtt.move_order_line_id = mol.line_id
-- Bug# 3434940 - Performance Fixes
-- Also join against org and LPN for MOL to speed up the parsing
-- time for the query
AND mol.organization_id = p_org_id
AND mol.lpn_id = l_lpn_id;
SELECT transaction_header_id
, transaction_temp_id
, inventory_item_id
, revision
, subinventory_code
, locator_id
, transaction_quantity
, transfer_to_location
, NVL(standard_operation_id, 2)
, task_priority
, NVL(wms_task_type, 2)
, operation_plan_id
, move_order_line_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = l_line_id
AND transaction_action_id NOT IN (50, 51, 52) --ATF_J3: to make sure dummy packing MMTT lines not selected
;
SELECT inventory_item_id
, quantity
, uom_code
, lot_number
, revision
, cost_group_id
, secondary_quantity -- Added for OPM convergance
, secondary_uom_code -- Added for OPM convergance
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn_id;
l_last_update_date DATE;
l_temp_update_date DATE;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT regeneration_interval
, NVL(crossdock_flag, 2) cdock
, pregen_putaway_tasks_flag
INTO l_regeneration_interval
, l_cdock_flag
, l_pregen_putaway_tasks_flag
FROM mtl_parameters
WHERE organization_id = p_org_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND organization_id = p_org_id;
SELECT 1
INTO l_rcount
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_txn_request_lines l, mtl_txn_request_headers h
WHERE l.lpn_id = l_lpn_id
AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- added for ATF_J
AND NVL(l.quantity_delivered, 0) < l.quantity -- added for ATF_J
AND l.organization_id = p_org_id
AND l.header_id = h.header_id
AND l.line_status <> inv_globals.g_to_status_closed
AND h.move_order_type = inv_globals.g_move_order_put_away);
SELECT subinventory_code
, locator_id
INTO l_m_sub
, l_m_loc
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id IN(
SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id IN(
SELECT mol.line_id
FROM mtl_txn_request_lines mol
WHERE mol.lpn_id = l_lpn_id
AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
AND mol.organization_id = p_org_id
AND mol.quantity_detailed > 0
AND EXISTS(
SELECT 1
FROM mtl_txn_request_headers moh
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = inv_globals.g_move_order_put_away)));
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE EXISTS(
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mtlt.transaction_temp_id = mmtt.transaction_temp_id
AND mmtt.move_order_line_id IN(
SELECT mol.line_id
FROM mtl_txn_request_lines mol
WHERE mol.lpn_id = l_lpn_id
AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
AND mol.organization_id = p_org_id
AND mol.quantity_detailed > 0
AND EXISTS(
SELECT 1
FROM mtl_txn_request_headers moh
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = inv_globals.g_move_order_put_away)));
DELETE FROM mtl_material_transactions_temp
WHERE move_order_line_id IN(
SELECT mol.line_id
FROM mtl_txn_request_lines mol
WHERE mol.lpn_id = l_lpn_id
AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
AND mol.organization_id = p_org_id
AND mol.quantity_detailed > 0
AND EXISTS(SELECT 1
FROM mtl_txn_request_headers moh
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = inv_globals.g_move_order_put_away));
UPDATE mtl_txn_request_lines mol
SET mol.line_status = inv_globals.g_to_status_closed
WHERE mol.lpn_id = l_lpn_id
AND mol.line_id = NVL(p_move_order_line_id, mol.line_id) -- added for ATF_J
AND mol.organization_id = p_org_id
-- AND mol.quantity_detailed > 0 -- removed in ATF_J3
AND EXISTS(SELECT 1
FROM mtl_txn_request_headers moh
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = inv_globals.g_move_order_put_away);
SELECT mil.project_id
, mil.task_id
INTO l_project_id
, l_task_id
FROM mtl_item_locations mil
WHERE mil.inventory_location_id = l_m_loc
AND mil.organization_id = p_org_id
AND mil.subinventory_code = l_m_sub;
UPDATE mtl_txn_request_lines mol
SET to_subinventory_code = p_subinventory
, to_locator_id = p_locator_id
, quantity_detailed = NULL
WHERE organization_id = p_org_id
AND lpn_id = l_lpn_id
AND mol.line_status <> inv_globals.g_to_status_closed;
l_temp_update_date := SYSDATE;
SELECT '1'
INTO wdt_exist
FROM DUAL
WHERE EXISTS(SELECT transaction_temp_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_pregen_suggestion.transaction_temp_id);
((SYSDATE - l_pregen_suggestion.last_update_date) * 24 * 60 > l_regeneration_interval)
OR -- MMTT line staled
l_pregen_putaway_tasks_flag <> 1 -- ATF_J5: also treat MMTT lines other than pre-generated as stale. This is to cover what cleanup_partial_putaway used to do
) -- MMTT was not generated by pregenerate (???)
AND l_mmtt_staled_flag = 'N' THEN
IF (l_pregen_suggestion.backorder_delivery_detail_id IS NULL -- not a backordered line
AND Nvl(l_pregen_suggestion.inspection_status, 2) <> 1) THEN -- not a line that requires inspection i.e. manually pre-gen
l_mmtt_staled_flag := 'Y';
END IF; -- (Sysdate - l_pregen_suggestion.last_update_date)*24*60
-- MMTT and MOL update from inbound UI for item load.
-- The original need for autonomous cleanup_suggestions
-- is satisfied by passing p_for_manual_drop => true
-- into abort_operation_instance .
wms_putaway_suggestions.cleanup_suggestions
(
p_lpn_id => l_lpn_id
, p_org_id => p_org_id
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_move_order_line_id => p_move_order_line_id
); --added for ATF_J2
SELECT 'Y'
INTO l_wlc_mol_missmatch_flag
FROM dual
WHERE exists
(SELECT wlc.inventory_item_id
FROM
(SELECT parent_lpn_id,
SUM(quantity) quantity,
uom_code,
inventory_item_id,
revision,
lot_number,
organization_id--BUG 4607833
FROM
wms_lpn_contents
GROUP BY parent_lpn_id, inventory_item_id, revision, lot_number,uom_code,organization_id--BUG 4607833
) wlc, -- sub-query is necessary because there could be more than one wlc record for the same inventory_item_id, revision, lot_number
mtl_txn_request_lines mol
WHERE wlc.parent_lpn_id = mol.lpn_id
AND wlc.inventory_item_id = mol.inventory_item_id
-- AND wlc.uom_code = mol.uom_code -- Bug fix 3200526
AND wlc.organization_id = mol.organization_id --Bug 4607833
AND (wlc.revision = mol.revision
OR(wlc.revision IS NULL AND mol.revision IS NULL)
)
AND (wlc.lot_number = mol.lot_number
OR(wlc.lot_number IS NULL AND mol.lot_number IS NULL)
)
AND mol.line_status <> 5 -- not closed
AND mol.lpn_id = l_lpn_id
-- AND mol.line_id = Nvl(p_move_order_line_id, mol.line_id) -- comment out in ATF_J3, we should only check mismatch based on LPN, because in item load pack/unpack happens after receiving TM call
-- Bug fix 3200526 If the MOLs for this LPN have different UOMs,
-- do not consider it mismatch, because we really don't want to call
-- UOM converstion for this pre-cautionary check.
GROUP BY wlc.inventory_item_id, wlc.lot_number, wlc.revision, wlc.uom_code
HAVING MIN(wlc.quantity) <> SUM(mol.quantity-Nvl(mol.quantity_delivered, 0))
AND MIN(mol.UOM_CODE) = MAX(mol.UOM_CODE)
AND wlc.UOM_CODE = MIN(mol.UOM_CODE)
);
UPDATE mtl_txn_request_lines
SET reference_detail_id = NULL
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines mol
SET mol.quantity_detailed = (SELECT NVL(mol.quantity_delivered, 0) + NVL(SUM(mmtt.transaction_quantity), 0)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = l_line_id
AND transaction_action_id NOT IN (50, 51, 52)
AND NOT (transaction_action_id = 2
AND transaction_source_type_id = 13) -- this is to make sure the dummy MMTT erwin created does not contribute this calculation
)
WHERE mol.line_id = l_line_id;
mydebug('suggestions_pub: After UPdate');
-- removed update WIP related info into MMTT, this should have been take care of by rules engine
-- }}
-- Check mtl statuses
IF (l_debug = 1) THEN
mydebug('suggestions_pub: Checking mtl_status');
/* nsinghi - GME-WMS Integration. Added the following select stmt to determine if Process Org.
Also added check in If statement to not refer to Wip_Lpn_Completions table for discrete Orgs.
Also added the If statement to update transaction_source_id in MMTT for Process Orgs. */
SELECT NVL(process_enabled_flag, 'N') INTO l_process_flag
FROM mtl_parameters WHERE organization_id = p_org_id;
SELECT completion_transaction_id
, DECODE(wip_entity_type, 4, 'Y', 'N')
, wip_entity_id
INTO l_completion_txn_id
, l_flow_schedule
, l_transaction_source_id
FROM wip_lpn_completions
WHERE header_id = l_ref_id;
UPDATE mtl_material_transactions_temp
SET completion_transaction_id = l_completion_txn_id
, flow_schedule = l_flow_schedule
, transaction_source_id = l_transaction_source_id
WHERE transaction_temp_id = l_transaction_temp_id;
/* nsinghi - added the If statement to update transaction_source_id in MMTT for Process Orgs. */
IF (l_txn_source_type_id = 5 AND l_process_flag = 'Y') THEN
SELECT txn_source_id INTO l_transaction_source_id
FROM mtl_txn_request_lines
WHERE line_id = l_line_id;
UPDATE mtl_material_transactions_temp
SET flow_schedule = 'N'
, transaction_source_id = l_transaction_source_id
WHERE transaction_temp_id = l_transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = ABS(transaction_quantity)
, primary_quantity = ABS(primary_quantity)
WHERE transaction_temp_id = l_transaction_temp_id;
SELECT COUNT(t.transaction_temp_id)
INTO l_rows_detailed
FROM mtl_material_transactions_temp t, mtl_txn_request_lines l
WHERE l.lpn_id = l_lpn_id
AND l.organization_id = p_org_id
AND l.line_id = NVL(p_move_order_line_id, l.line_id) -- Added for ATF_J
AND l.line_id = t.move_order_line_id;
/* Local function to insert record in MTL_SERIAL_NUMBERS_TEMP given
* a record type containing the information of one serial number (including
* attributes). This procedure is called from complete_putaway to create
* as many MSNT records for the serial numbers within the LPN for the quantity
* confirmed
*/
FUNCTION insert_msnt_rec(
p_transaction_temp_id IN NUMBER
, p_serial_number IN VARCHAR2
, p_serial_atts IN msn_attribute_rec_tp
, p_user_id IN NUMBER
, p_to_serial_number IN VARCHAR2 DEFAULT NULL) RETURN BOOLEAN IS
BEGIN
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
)
VALUES (
p_transaction_temp_id
, SYSDATE
, p_user_id
, SYSDATE
, p_user_id
, p_serial_number
, Nvl(p_to_serial_number,p_serial_number)
, p_serial_atts.vendor_serial_number
, p_serial_atts.vendor_lot_number
, p_serial_atts.parent_serial_number
, p_serial_atts.origination_date
, p_serial_atts.end_item_unit_number
, p_serial_atts.territory_code
, p_serial_atts.time_since_new
, p_serial_atts.cycles_since_new
, p_serial_atts.time_since_overhaul
, p_serial_atts.cycles_since_overhaul
, p_serial_atts.time_since_repair
, p_serial_atts.cycles_since_repair
, p_serial_atts.time_since_visit
, p_serial_atts.cycles_since_visit
, p_serial_atts.time_since_mark
, p_serial_atts.cycles_since_mark
, p_serial_atts.number_of_repairs
, p_serial_atts.serial_attribute_category
, p_serial_atts.c_attribute1
, p_serial_atts.c_attribute2
, p_serial_atts.c_attribute3
, p_serial_atts.c_attribute4
, p_serial_atts.c_attribute5
, p_serial_atts.c_attribute6
, p_serial_atts.c_attribute7
, p_serial_atts.c_attribute8
, p_serial_atts.c_attribute9
, p_serial_atts.c_attribute10
, p_serial_atts.c_attribute11
, p_serial_atts.c_attribute12
, p_serial_atts.c_attribute13
, p_serial_atts.c_attribute14
, p_serial_atts.c_attribute15
, p_serial_atts.c_attribute16
, p_serial_atts.c_attribute17
, p_serial_atts.c_attribute18
, p_serial_atts.c_attribute19
, p_serial_atts.c_attribute20
, p_serial_atts.d_attribute1
, p_serial_atts.d_attribute2
, p_serial_atts.d_attribute3
, p_serial_atts.d_attribute4
, p_serial_atts.d_attribute5
, p_serial_atts.d_attribute6
, p_serial_atts.d_attribute7
, p_serial_atts.d_attribute8
, p_serial_atts.d_attribute9
, p_serial_atts.d_attribute10
, p_serial_atts.n_attribute1
, p_serial_atts.n_attribute2
, p_serial_atts.n_attribute3
, p_serial_atts.n_attribute4
, p_serial_atts.n_attribute5
, p_serial_atts.n_attribute6
, p_serial_atts.n_attribute7
, p_serial_atts.n_attribute8
, p_serial_atts.n_attribute9
, p_serial_atts.n_attribute10
);
END insert_msnt_rec;
/* Local function to insert a record in MTL_TRANSACTION_LOTS_TEMP
* from the original MTLT record. These MTLT records are needed by
* the receiving transaction manager for the deliver transaction
*/
FUNCTION insert_dup_mtlt (
p_orig_temp_id IN NUMBER
, p_new_temp_id IN NUMBER
, p_serial_temp_id IN NUMBER
, p_item_id IN NUMBER
, p_organization_id IN NUMBER) RETURN BOOLEAN IS
BEGIN
INSERT INTO mtl_transaction_lots_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, transaction_quantity
, primary_quantity
, lot_number
, lot_expiration_date
, serial_transaction_temp_id
, description
, vendor_name
, supplier_lot_number
, origination_date
, date_code
, grade_code
, change_date
, maturity_date
, retest_date
, age
, item_size
, color
, volume
, volume_uom
, place_of_origin
, best_by_date
, LENGTH
, length_uom
, recycled_content
, thickness
, thickness_uom
, width
, width_uom
, curl_wrinkle_fold
, lot_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
, vendor_id
, territory_code
)
(SELECT p_new_temp_id
, mtlt.last_update_date
, mtlt.last_updated_by
, mtlt.creation_date
, mtlt.created_by
, mtlt.transaction_quantity
, mtlt.primary_quantity
, mtlt.lot_number
, mtlt.lot_expiration_date
, p_serial_temp_id
, mln.description
, mln.vendor_name
, mln.supplier_lot_number
, mln.origination_date
, mln.date_code
, mln.grade_code
, mln.change_date
, mln.maturity_date
, mln.retest_date
, mln.age
, mln.item_size
, mln.color
, mln.volume
, mln.volume_uom
, mln.place_of_origin
, mln.best_by_date
, mln.LENGTH
, mln.length_uom
, mln.recycled_content
, mln.thickness
, mln.thickness_uom
, mln.width
, mln.width_uom
, mln.curl_wrinkle_fold
, mln.lot_attribute_category
, mln.c_attribute1
, mln.c_attribute2
, mln.c_attribute3
, mln.c_attribute4
, mln.c_attribute5
, mln.c_attribute6
, mln.c_attribute7
, mln.c_attribute8
, mln.c_attribute9
, mln.c_attribute10
, mln.c_attribute11
, mln.c_attribute12
, mln.c_attribute13
, mln.c_attribute14
, mln.c_attribute15
, mln.c_attribute16
, mln.c_attribute17
, mln.c_attribute18
, mln.c_attribute19
, mln.c_attribute20
, mln.d_attribute1
, mln.d_attribute2
, mln.d_attribute3
, mln.d_attribute4
, mln.d_attribute5
, mln.d_attribute6
, mln.d_attribute7
, mln.d_attribute8
, mln.d_attribute9
, mln.d_attribute10
, mln.n_attribute1
, mln.n_attribute2
, mln.n_attribute3
, mln.n_attribute4
, mln.n_attribute5
, mln.n_attribute6
, mln.n_attribute7
, mln.n_attribute8
, mln.n_attribute9
, mln.n_attribute10
, mln.vendor_id
, mln.territory_code
FROM mtl_transaction_lots_temp mtlt
, mtl_lot_numbers mln
WHERE mtlt.transaction_temp_id = p_orig_temp_id
AND mln.lot_number = mtlt.lot_number
AND mln.inventory_item_id = p_item_id
AND mln.organization_id = p_organization_id);
END insert_dup_mtlt;
/* Local function to insert records in MTL_SERIAL_NUMBERS_TEMP
* from the original MSNT record(s). These records are needed by
* the receiving transaction manager for the deliver transaction
*/
FUNCTION insert_dup_msnt (
p_orig_temp_id IN NUMBER
, p_new_temp_id IN NUMBER) RETURN BOOLEAN IS
BEGIN
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
)
(SELECT p_new_temp_id
, last_update_date
, last_updated_by
, creation_date
, created_by
, fm_serial_number
, to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_orig_temp_id);
END insert_dup_msnt;
FUNCTION insert_mtli_helper(
p_txn_if_id IN OUT NOCOPY NUMBER
, p_lot_number IN VARCHAR2
, p_txn_qty IN NUMBER
, p_prm_qty IN NUMBER
, p_item_id IN NUMBER
, p_org_id IN NUMBER
, x_serial_temp_id OUT NOCOPY NUMBER
, p_product_txn_id IN OUT NOCOPY NUMBER
, p_temp_id IN NUMBER
, p_secondary_quantity IN NUMBER --OPM Convergence
, p_secondary_uom IN NUMBER --OPM Convergence
) RETURN BOOLEAN IS
--Local variables
l_lot_status_id NUMBER;
SELECT expiration_date
, status_id
INTO l_expiration_date
, l_lot_status_id
FROM mtl_lot_numbers
WHERE lot_number = p_lot_number
AND inventory_item_id = p_item_id
AND organization_id = p_org_id;
inv_rcv_integration_apis.insert_mtli(
p_api_version => 1.0
, p_init_msg_lst => l_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_txn_if_id
, p_lot_number => p_lot_number
, p_transaction_quantity => p_txn_qty
, p_primary_quantity => p_prm_qty
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_expiration_date => l_expiration_date
, p_status_id => l_lot_status_id
, x_serial_transaction_temp_id => x_serial_temp_id
, p_product_transaction_id => l_product_txn_id
, p_product_code => l_prod_code
, p_att_exist => l_yes
, p_update_mln => l_no
, p_secondary_quantity => p_secondary_quantity --OPM Convergence
);
mydebug('insert_mtli_helper: Error occurred while creating interface lots: ' || l_msg_data);
mydebug('Exception occurred in insert_mtli_helper: ');
END insert_mtli_helper;
FUNCTION insert_msni_helper(
p_txn_if_id IN OUT NOCOPY NUMBER
, p_serial_number IN VARCHAR2
, p_item_id IN NUMBER
, p_org_id IN NUMBER
, p_product_txn_id IN OUT NOCOPY NUMBER
) RETURN BOOLEAN IS
--Local variables
l_serial_status_id NUMBER;
SELECT status_id
INTO l_serial_status_id
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_item_id;
inv_rcv_integration_apis.insert_msni(
p_api_version => 1.0
, p_init_msg_lst => l_false
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_transaction_interface_id => l_txn_if_id
, p_fm_serial_number => p_serial_number
, p_to_serial_number => p_serial_number
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_status_id => l_serial_status_id
, p_product_transaction_id => l_product_txn_id
, p_product_code => l_prod_code
, p_att_exist => l_yes
, p_update_msn => l_no);
mydebug('insert_msni_helper: Error occurred while creating interface serials: ' || l_msg_data);
mydebug('Exception occurred in insert_msni_helper: ');
END insert_msni_helper;
select primary_cost_method
into l_primary_cost_method
from mtl_parameters
where organization_id = p_org_id;
SELECT serial_number
, serial_number to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '#%^') = NVL(p_rev, '#%^')
AND NVL(lot_number, -999) = NVL(p_lot, -999)
AND ROWNUM <= l_pr_qty
AND (
(p_entire_lpn = 'Y'
AND(group_mark_id IS NULL
OR group_mark_id = -1))
OR(p_entire_lpn = 'N'
AND group_mark_id = 1)
OR(p_process_serial_flag = 'N'
AND p_entire_lpn = 'N'
AND group_mark_id IS NULL)
)
-- Bug# 2772676
-- For WIP completions, there is a specific serial
-- tied to the MOL/MMTT line which we have to use
AND (
(l_transaction_source_type_id = 5
AND serial_number IN(SELECT fm_serial_number
FROM wip_lpn_completions_serials
WHERE header_id = l_ref_id))
OR l_transaction_source_type_id <> 5
);
SELECT serial_number ,
serial_number to_serial_number ,
vendor_serial_number ,
vendor_lot_number ,
parent_serial_number ,
origination_date ,
end_item_unit_number ,
territory_code ,
time_since_new ,
cycles_since_new ,
time_since_overhaul ,
cycles_since_overhaul ,
time_since_repair ,
cycles_since_repair ,
time_since_visit ,
cycles_since_visit ,
time_since_mark ,
cycles_since_mark ,
number_of_repairs ,
serial_attribute_category ,
c_attribute1 ,
c_attribute2 ,
c_attribute3 ,
c_attribute4 ,
c_attribute5 ,
c_attribute6 ,
c_attribute7 ,
c_attribute8 ,
c_attribute9 ,
c_attribute10 ,
c_attribute11 ,
c_attribute12 ,
c_attribute13 ,
c_attribute14 ,
c_attribute15 ,
c_attribute16 ,
c_attribute17 ,
c_attribute18 ,
c_attribute19 ,
c_attribute20 ,
d_attribute1 ,
d_attribute2 ,
d_attribute3 ,
d_attribute4 ,
d_attribute5 ,
d_attribute6 ,
d_attribute7 ,
d_attribute8 ,
d_attribute9 ,
d_attribute10 ,
n_attribute1 ,
n_attribute2 ,
n_attribute3 ,
n_attribute4 ,
n_attribute5 ,
n_attribute6 ,
n_attribute7 ,
n_attribute8 ,
n_attribute9 ,
n_attribute10
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn_id
AND inventory_item_id = p_item_id
AND NVL(revision, '#%^') = NVL(p_rev, '#%^')
AND NVL(lot_number, -999) = NVL(p_lot, -999)
AND ROWNUM <= l_pr_qty
AND
(
(
p_entire_lpn = 'Y'
AND
(
group_mark_id IS NULL
OR group_mark_id >= -1
)
)
OR
(
p_entire_lpn = 'N'
AND group_mark_id = 1
)
OR
(
p_process_serial_flag = 'N'
AND p_entire_lpn = 'N'
AND group_mark_id IS NULL
)
)
-- Bug# 2772676
-- For WIP completions, there is a specific serial
-- tied to the MOL/MMTT line which we have to use
AND
(
(
l_transaction_source_type_id = 5
AND serial_number IN
(
SELECT fm_serial_number
FROM wip_lpn_completions_serials
WHERE header_id = l_ref_id
)
)
OR l_transaction_source_type_id <> 5
);
SELECT serial_number
, serial_number to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_serial_numbers msn
WHERE msn.lpn_id = p_lpn_id
AND msn.inventory_item_id = p_item_id
AND ((p_rev IS NOT NULL AND msn.revision = p_rev)
OR (p_rev IS NULL AND msn.revision IS NULL))
AND ((p_lot IS NOT NULL AND msn.lot_number = p_lot)
OR (p_lot IS NULL AND msn.lot_number IS NULL))
AND ROWNUM <= l_pr_qty
AND (
(p_entire_lpn = 'Y'
AND(msn.group_mark_id IS NULL OR msn.group_mark_id = -1)
)
OR(p_entire_lpn = 'N' AND group_mark_id = 1)
OR(p_process_serial_flag = 'N'
AND p_entire_lpn = 'N'
AND group_mark_id IS NULL)
)
ORDER BY msn.lot_number, msn.serial_number;
SELECT fm_serial_number serial_number
, to_serial_number to_serial_number
, vendor_serial_number
, vendor_lot_number
, parent_serial_number
, origination_date
, NULL end_item_unit_number
, territory_code
, time_since_new
, cycles_since_new
, time_since_overhaul
, cycles_since_overhaul
, time_since_repair
, cycles_since_repair
, time_since_visit
, cycles_since_visit
, time_since_mark
, cycles_since_mark
, number_of_repairs
, serial_attribute_category
, c_attribute1
, c_attribute2
, c_attribute3
, c_attribute4
, c_attribute5
, c_attribute6
, c_attribute7
, c_attribute8
, c_attribute9
, c_attribute10
, c_attribute11
, c_attribute12
, c_attribute13
, c_attribute14
, c_attribute15
, c_attribute16
, c_attribute17
, c_attribute18
, c_attribute19
, c_attribute20
, d_attribute1
, d_attribute2
, d_attribute3
, d_attribute4
, d_attribute5
, d_attribute6
, d_attribute7
, d_attribute8
, d_attribute9
, d_attribute10
, n_attribute1
, n_attribute2
, n_attribute3
, n_attribute4
, n_attribute5
, n_attribute6
, n_attribute7
, n_attribute8
, n_attribute9
, n_attribute10
FROM mtl_serial_numbers_interface msni
-- Bug# 3281512 - Performance Fixes
-- Since this cursor is only used for receiving, the product code
-- will always be 'RCV'. This is needed in order to use the index.
WHERE msni.product_code = 'RCV'
AND msni.product_transaction_id = v_prod_txn_id;
SELECT NVL(process_enabled_flag, 'N') INTO l_process_flag
FROM mtl_parameters WHERE organization_id = p_org_id;
SELECT task_id
INTO l_task_id
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_temp_id
AND ROWNUM < 2; -- prevent exception
SELECT mol.REFERENCE
, mol.reference_type_code
, mol.reference_id
, mol.backorder_delivery_detail_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transaction_action_id
, mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mmtt.transaction_header_id
, mol.transaction_source_type_id
, mol.inspection_status
, mol.lpn_id
, mmtt.transaction_uom
, mmtt.transaction_quantity
, mmtt.wip_supply_type
, mol.crossdock_type
, mol.txn_source_id
, mol.lot_number
, mol.line_id
, mmtt.operation_plan_id
, mmtt.transaction_type_id
, mmtt.secondary_transaction_quantity --OPM Convergence
, mol.to_cost_group_id --BUG 4134432
, mmtt.secondary_uom_code --OPM Convergence
INTO l_ref
, l_ref_type
, l_ref_id
, l_del_detail_id
, l_org_sub
, l_org_loc
, l_txn_action_id
, l_dest_sub
, l_dest_loc_id
, l_orig_txn_header_id
, l_transaction_source_type_id
, l_inspection_status
, l_mo_lpn_id
, l_orig_txn_uom
, l_orig_qty
, l_wip_supply_type
, l_crossdock_type
, l_parent_txn_id
, l_mol_lot_number
, l_mo_line_id
, l_operation_plan_id
, l_txn_type_id
, l_secondary_qty --OPM Convergence
, l_cost_group_id -- BUG 4134432
, l_secondary_uom_code --OPM Convergence
FROM mtl_txn_request_lines mol
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.move_order_line_id = mol.line_id;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM DUAL;
mydebug('complete_putaway: Uom has changed - need to update MMTT');
UPDATE mtl_material_transactions_temp
SET transaction_uom = p_uom
, transaction_quantity = l_qty
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = p_sub
, transfer_to_location = p_loc
WHERE transaction_temp_id = p_temp_id;
mydebug('complete_putaway: RCV LPN, No updates of sub and loc for receving lpn');
UPDATE mtl_material_transactions_temp
SET subinventory_code = p_sub
, locator_id = p_loc
WHERE transaction_temp_id = p_temp_id;
SELECT 1
INTO l_exist_lpn
FROM DUAL
WHERE EXISTS(SELECT 1
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id);
SELECT lpn_id
INTO l_to_lpn_id
FROM wms_license_plate_numbers
WHERE license_plate_number = p_to_lpn
AND organization_id = p_org_id;
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_code
, l_serial_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT count(1)
INTO l_is_msni_req
FROM mtl_serial_numbers
WHERE inventory_item_id = p_item_id
AND lpn_id = p_lpn_id
AND current_status = 7
AND current_organization_id = p_org_id;
mydebug('complete_putaway: Inserting Lots');
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_ser_seq
FROM DUAL;
* Do not update MTL_TRANSACTION_LOTS_TEMP from here if the LPN Resides in
* Receiving and WMS and PO patch levels are J or higher
* For other LPN contexts and if patch levels are lower than J,
* continue with the updates
*/
IF (l_lpn_context = 3) THEN
IF (l_is_crossdocked = FALSE) THEN
IF l_debug = 1 THEN
mydebug('complete_putaway: LPN Resides in Receiving. No updates to MTLT from here.');
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = l_ser_seq
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_temp_id
AND lot_number = p_lot;
mydebug('complete_putaway: INV/WIP LPN. Update MTLT.');
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_orig_qty
, primary_quantity = l_pr_qty
, serial_transaction_temp_id = l_ser_seq
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_temp_id
AND lot_number = p_lot;
Update Lot attrs from wip_lpn_completions_lots table only for discrete orgs. */
IF (l_transaction_source_type_id = 5 AND l_process_flag = 'N') THEN
IF (l_debug = 1) THEN
mydebug('complete_putaway: Capture lot atts from wip tables');
-- Update MMTT for WIP flow completions
SELECT DECODE(wip_entity_type, 4, 'Y', 'N')
INTO l_flow_schedule
FROM wip_lpn_completions
WHERE header_id = l_ref_id;
UPDATE mtl_material_transactions_temp
SET flow_schedule = l_flow_schedule
WHERE transaction_temp_id = p_temp_id;
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_product_transaction_id
FROM sys.dual;
l_result := insert_mtli_helper(
p_txn_if_id => l_dup_temp_id
, p_lot_number => p_lot
, p_txn_qty => p_qty --Bug 5225012. Earlier l_orig_qty was being passed
, p_prm_qty => l_pr_qty
, p_item_id => p_item_id
, p_org_id => p_org_id
, x_serial_temp_id => l_dup_ser_temp_id
, p_product_txn_id => l_product_transaction_id
, p_temp_id => p_temp_id
, p_secondary_quantity => p_secondary_quantity --OPM Convergence
, p_secondary_uom => p_secondary_uom); --OPM Convergence
mydebug('complete_putaway: Failure while Inserting MSNI records - lot and serial controlled item');
mydebug('complete_putaway: Inserted MTLI for lot and serial item. intf_txn_id: ' || l_dup_temp_id ||
', ser_temp_id : ' || l_dup_ser_temp_id || ' , prod_txn_id: ' || l_product_transaction_id);
mydebug('complete_putaway: Inserting Serials');
SELECT COUNT(msn.group_mark_id)
INTO res_count
FROM mtl_serial_numbers msn ,
mtl_reservations mr
WHERE mr.lpn_id =msn.lpn_id
AND msn.group_mark_id=mr.reservation_id
AND msn.lpn_id = p_lpn_id;
Update Serial attrs from wip_lpn_completions_serials table only for discrete orgs. */
IF (l_transaction_source_type_id = 5 AND l_process_flag = 'N') THEN
IF (l_debug = 1) THEN
mydebug('complete_putaway: Capture serial atts from wip tables');
, p_last_update_date => SYSDATE
, p_last_updated_by => p_user_id
, p_creation_date => SYSDATE
, p_created_by => p_user_id
, p_fm_serial_number => l_serial
, p_to_serial_number => l_serial
, p_serial_temp_id => l_ser_seq
, p_serial_flag => 2
);
mydebug('complete_putaway: Inserting MSNI - lot and serial item');
l_result := insert_msni_helper(
p_txn_if_id => l_dup_ser_temp_id
, p_serial_number => l_serial
, p_org_id => p_org_id
, p_item_id => p_item_id
, p_product_txn_id => l_product_transaction_id
);
mydebug('complete_putaway: Failure while Inserting MSNI records - lot and serial controlled item');
mydebug('complete_putaway: Inserted MSNI for lot and serial item. intf_txn_id: '
|| l_dup_ser_temp_id || ' , prod_txn_id: ' || l_product_transaction_id);
mydebug('complete_putaway: Inserting MSNT for xdock - lot and serial item' || l_ser_seq);
l_result := insert_msnt_rec(
p_transaction_temp_id => l_ser_seq
, p_serial_number => l_serial
, p_serial_atts => l_serial_rec
, p_user_id => p_user_id);
END IF; --END IF insert MSNT for xdock
mydebug('complete_putaway: Inserting MSNT - lot and serial item' || l_ser_seq);
l_result := insert_msnt_rec(
p_transaction_temp_id => l_ser_seq
, p_serial_number => l_serial
, p_serial_atts => l_serial_rec
, p_user_id => p_user_id);
* Do not update MTL_SERIAL_NUMBERS from here if the LPN Resides in
* Receiving and WMS and PO patch levels are J or higher
* For other LPN contexts and if patch levels are lower than J,
* continue with the updates
*/
IF (l_lpn_context = 3) THEN
IF l_debug = 1 THEN
mydebug('complete_putaway: The LPN resides in Receiving. No updates to MSN from here.');
mydebug('complete_putaway: INV/WIP LPN. Update MSN.');
UPDATE mtl_serial_numbers
SET group_mark_id = p_temp_id
WHERE serial_number = l_serial
AND inventory_item_id = p_item_id
AND lot_number = p_lot;
UPDATE mtl_serial_numbers
SET current_status = 4
, previous_status = current_status
WHERE serial_number = l_serial
AND inventory_item_id = p_item_id
AND lot_number = p_lot;
SELECT COUNT(msn.group_mark_id)
INTO res_count
FROM mtl_serial_numbers msn ,
mtl_reservations mr
WHERE mr.lpn_id =msn.lpn_id
AND msn.group_mark_id=mr.reservation_id
AND msn.lpn_id = p_lpn_id;
, p_last_update_date => SYSDATE
, p_last_updated_by => p_user_id
, p_creation_date => SYSDATE
, p_created_by => p_user_id
, p_fm_serial_number => l_serial
, p_to_serial_number => l_serial
, p_serial_temp_id => p_temp_id
, p_serial_flag => 3
);
mydebug('complete_putaway: Inserting MSNI - serial controlled only' || p_temp_id);
SELECT rcv_transactions_interface_s.NEXTVAL
INTO l_product_transaction_id
FROM sys.dual;
l_result := insert_msni_helper(
p_txn_if_id => l_dup_temp_id
, p_serial_number => l_serial
, p_org_id => p_org_id
, p_item_id => p_item_id
, p_product_txn_id => l_product_transaction_id);
mydebug('complete_putaway: Failure while Inserting MSNI records - serial controlled item');
mydebug('complete_putaway: Inserting MSNT for xdock - serial item' || l_ser_seq);
l_result := insert_msnt_rec(
p_transaction_temp_id => p_temp_id
, p_serial_number => l_serial
, p_serial_atts => l_serial_rec
, p_user_id => p_user_id);
mydebug('complete_putaway: Inserting MSNT - serial controlled only' || p_temp_id);
SELECT count(transaction_temp_id)
INTO l_exist_msnt
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_temp_id
AND fm_serial_number = l_serial ;
l_result := insert_msnt_rec(
p_transaction_temp_id => p_temp_id
, p_serial_number => l_serial
, p_serial_atts => l_serial_rec
, p_user_id => p_user_id);
mydebug('complete_putaway: Failure while Inserting MSNT - serial controlled only');
* Do not update MTL_SERIAL_NUMBERS from here if the LPN Resides in
* Receiving and WMS and PO patch levels are J or higher
* For other LPN contexts and if patch levels are lower than J,
* continue with the updates
*/
IF (l_lpn_context = 3) THEN
IF l_debug =1 THEN
mydebug('complete_putaway: LPN Resides in Receiving. No updates to MSN from here.');
UPDATE mtl_serial_numbers
SET group_mark_id = p_temp_id
WHERE serial_number = l_serial
AND inventory_item_id = p_item_id;
UPDATE mtl_serial_numbers
SET current_status = 4
, previous_status = current_status
WHERE serial_number = l_serial
AND inventory_item_id = p_item_id;
SELECT serial_transaction_temp_id
INTO l_msnt_temp_id
FROM mtl_transaction_lots_interface
WHERE lot_number = p_lot
AND product_transaction_id = l_product_transaction_id;
l_result := insert_msnt_rec(
p_transaction_temp_id => l_msnt_temp_id
, p_serial_number => l_fm_serial
, p_serial_atts => l_serial_rec
, p_user_id => p_user_id
, p_to_serial_number => l_to_serial);
mydebug('complete_putaway: Failure while Inserting MSNT - qty disc');
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = l_qty
, primary_quantity = l_pr_qty
, last_update_date = SYSDATE
, last_updated_by = p_user_id
WHERE transaction_temp_id = p_temp_id
AND lot_number = p_lot;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = p_temp_id;
--This is Receiving LPN, we have to update header_id as we need
-- one for each txn
--We always will generate new txn header id for a receiving lpn due to the
--problem in join between WDTH and MMT for the deliver transaction
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = p_temp_id;
mydebug('Update only transfer LPN ID to NULL');
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
WHERE transaction_temp_id = p_temp_id;
UPDATE wms_exceptions
SET transaction_header_id = l_txn_header_id
WHERE transaction_header_id = l_orig_txn_header_id
AND organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = p_qty_reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
update mtl_material_transactions_temp
set reason_id = p_loc_reason_id
where transaction_temp_id = p_temp_id;
SELECT 1,
workflow_process
INTO l_wf,
l_wf_process
FROM mtl_transaction_reasons
WHERE reason_id = p_loc_reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
UPDATE mtl_material_transactions_temp
SET wms_task_type = -1
WHERE transaction_temp_id = p_temp_id;
UPDATE mtl_txn_request_lines
SET wms_process_flag = 2
WHERE line_id = l_mo_line_id;
UPDATE wms_dispatched_tasks
SET task_group_id = inv_rcv_common_apis.g_rcv_global_var.interface_group_id
WHERE transaction_temp_id = p_temp_id;
mydebug(' need to update the context of the from lpn ');
SELECT 1
INTO cnt
FROM DUAL
WHERE EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE lpn_id = p_lpn_id
AND organization_id = p_org_id
AND inventory_item_id = p_item_id)
OR EXISTS (SELECT 1
FROM mtl_material_transactions_temp
WHERE content_lpn_id = p_lpn_id
AND organization_id = p_org_id
AND inventory_item_id = p_item_id);
mydebug('complete_putaway: Patchset J or above , Update MMTT');
mydebug(' Update MMTT l_dest_loc_id with ' || l_dest_loc_id);
mydebug(' Update MMTT l_dest_sub with ' || l_dest_sub);
mydebug(' Update MMTT contet LPN_ID WITH ' || p_lpn_id);
mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
, content_lpn_id = p_lpn_id
, lpn_id = NULL
, transaction_status = 3
, posting_flag = 'Y'
, cost_group_id = Decode(l_lpn_context,1,l_cost_group_id,cost_group_id) --BUG 4134432,4475607
, transfer_cost_group_id = Decode(l_lpn_context,1,l_cost_group_id,transfer_cost_group_id) --BUG 4134432, 4475607
, transaction_date = sysdate --added per Karun and Saju's request 04/2006
, acct_period_id = l_acct_period_id --Added for bug 5403420
WHERE transaction_temp_id = p_temp_id;
mydebug(' Update MMTT from lpn_id with ' || p_lpn_id);
mydebug(' Update MMTT to lpn_id with ' || l_to_lpn_id);
mydebug(' Update MMTT cost_group_id with ' || l_cost_group_id);
UPDATE mtl_material_transactions_temp
SET transaction_header_id = l_txn_header_id
, lpn_id = p_lpn_id
, content_lpn_id = NULL
, transfer_lpn_id = l_to_lpn_id
, transaction_status = 3
, posting_flag = 'Y'
, cost_group_id = Decode(l_lpn_context,1,l_cost_group_id,cost_group_id) --BUG 4134432, 4475607
, transfer_cost_group_id = Decode(l_lpn_context,1,l_cost_group_id,transfer_cost_group_id) --BUG 4134432,4475607
, transaction_date = sysdate --added per Karun and Saju's request 04/2006
, acct_period_id = l_acct_period_id --Added for bug 5403420
WHERE transaction_temp_id = p_temp_id;
UPDATE wms_exceptions
SET transaction_header_id = l_txn_header_id
WHERE transaction_header_id = l_orig_txn_header_id
AND organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT 1
INTO l_wf
FROM mtl_transaction_reasons
WHERE reason_id = p_qty_reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
update mtl_material_transactions_temp
set reason_id = p_loc_reason_id
where transaction_temp_id = p_temp_id;
SELECT 1,
workflow_process
INTO l_wf,
l_wf_process
FROM mtl_transaction_reasons
WHERE reason_id = p_loc_reason_id
AND workflow_name IS NOT NULL
AND workflow_name <> ' '
AND workflow_process IS NOT NULL
AND workflow_process <> ' ';
SELECT transaction_quantity
, transaction_uom
, inventory_item_id
, move_order_line_id
INTO l_orig_qty
, l_orig_uom
, l_item_id
, l_line_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id
AND organization_id = l_org_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_qty_diff
, primary_quantity = primary_quantity - l_qty_diff_prim
WHERE transaction_temp_id = l_temp_id
AND organization_id = l_org_id;
* Do not update MTLT again since they would already have been done before
* coming here */
/*
BEGIN
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity-l_qty_diff,
primary_quantity = primary_quantity-l_qty_diff_prim
WHERE transaction_temp_id = l_temp_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_qty_diff
WHERE line_id = l_line_id
AND organization_id = l_org_id;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
,mtl_txn_request_lines mtrl
WHERE mtrl.line_id = mmtt.move_order_line_id
AND mtrl.line_status = 7
AND mtrl.lpn_id = p_lpn_id
AND mtrl.organization_id = p_org_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND organization_id = l_org_id;
SELECT lpn_id
INTO l_dummy
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id
AND organization_id = l_org_id
FOR UPDATE NOWAIT;
SELECT 1
INTO l_mo_cnt
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = l_lpn_id
AND organization_id = l_org_id
);
SELECT 1
INTO l_process_flag_cnt
FROM DUAL
WHERE EXISTS(SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = l_lpn_id
AND organization_id = l_org_id
AND NVL(wms_process_flag, 1) = 2
AND line_status <> 5); -- 3773255
SELECT 1
INTO l_so_cnt
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wsh_delivery_details_ob_grp_v wdd, wsh_delivery_assignments_v wda
WHERE wdd.lpn_id = l_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = l_org_id
AND wdd.delivery_detail_id = wda.parent_delivery_id);
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT 1
INTO l_mo_cnt2
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id = l_lpn_id
AND mtrl.organization_id = l_org_id
AND mtrl.line_id = mmtt.move_order_line_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.status <> 4
AND NOT (wdt.status = 3 AND wdt.person_id = l_emp_id ));--Bug# 3116925
SELECT 1 INTO l_mo_cnt2 FROM DUAL WHERE exists
(SELECT 1
FROM mtl_txn_request_lines mol,
mtl_material_transactions_temp mmtt
, wms_dispatched_tasks wdt
WHERE mol.lpn_id=l_lpn_id
AND mol.organization_id=l_org_id
AND mol.line_id=mmtt.move_order_line_id
AND wdt.transaction_temp_id=mmtt.transaction_temp_id
);
SELECT move_order_line_id
, transaction_quantity
, transaction_header_id
, transaction_batch_id
, transaction_batch_seq
INTO l_mmtt_line_id
, l_mmtt_qty
, l_txn_header_id
, l_transaction_batch_id
, l_transaction_batch_seq
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id
AND organization_id = l_org_id;
wms_insert_wdth_pvt.insert_into_wdth
( x_return_status => l_return_status
, p_txn_header_id => l_txn_header_id
, p_transaction_temp_id => l_temp_id
, p_transaction_batch_id => l_transaction_batch_id
, p_transaction_batch_seq => l_transaction_batch_seq
, p_transfer_lpn_id => NULL
);
mydebug ('Error from wms_insert_wdth_pvt.insert_into_wdth');
mydebug('After wmsdt update');
DELETE wms_dispatched_tasks
WHERE transaction_temp_id = l_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = (SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id);
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = l_temp_id;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
, p_delete_mmtt_flag IN VARCHAR2
, p_txn_header_id IN NUMBER
, p_transfer_lpn_id IN NUMBER DEFAULT NULL
) IS
l_temp_id NUMBER;
/* Bug 3961107-Modified the query to select transaction_batch_id and transaction_batch_seq
as null if they have null values in the table.
SELECT NVL(transaction_batch_id, -999)
, NVL(transaction_batch_seq, -999)*/
SELECT transaction_batch_id,
transaction_batch_seq
--End of fix for Bug 3961107
INTO l_transaction_batch_id
, l_transaction_batch_seq
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id
AND organization_id = l_org_id;
wms_insert_wdth_pvt.insert_into_wdth
( x_return_status => l_return_status
, p_txn_header_id => p_txn_header_id
, p_transaction_temp_id => l_temp_id
, p_transaction_batch_id => l_transaction_batch_id
, p_transaction_batch_seq => l_transaction_batch_seq
, p_transfer_lpn_id => p_transfer_lpn_id
);
mydebug ('Error from wms_insert_wdth_pvt.insert_into_wdth');
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_temp_id;
mydebug('archive_task: After wmsdt update');
IF p_delete_mmtt_flag = 'Y' THEN
-- Delete lot and serial records
BEGIN
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id IN
(SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id);
DELETE mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id;
DELETE mtl_serial_numbers_temp
WHERE transaction_temp_id = l_temp_id;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_temp_id;
l_last_updated_by NUMBER;
SELECT serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id;
SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = v_transaction_temp_id;
SELECT t.move_order_line_id
, t.transaction_quantity
, t.inventory_item_id
, t.primary_quantity
, w.person_id
, t.locator_id
, t.subinventory_code
, t.lot_number
, t.revision
, t.last_updated_by
, t.demand_source_header_id
, t.repetitive_line_id
, t.operation_seq_num
, t.secondary_transaction_quantity --OPM Convergence
INTO l_mmtt_line_id
, l_mmtt_qty
, l_item_id
, l_primary_qty
, l_person_id
, l_loc_id
, l_sub
, l_lot
, l_rev
, l_last_updated_by
, l_demand_source_header_id
, l_repetitive_line_id
, l_operation_seq_num
, l_secondary_quantity --OPM Convergence
FROM mtl_material_transactions_temp t, wms_dispatched_tasks w
WHERE t.transaction_temp_id = l_temp_id
AND t.organization_id = l_org_id
AND t.transaction_temp_id = w.transaction_temp_id;
SELECT wms_exceptions_s.NEXTVAL
INTO l_sequence
FROM DUAL;
mydebug('Inserting into exceptions');
INSERT INTO wms_exceptions
(
task_id
, sequence_number
, organization_id
, inventory_item_id
, person_id
, effective_start_date
, effective_end_date
, inventory_location_id
, reason_id
, discrepancy_type
, subinventory_code
, lot_number
, revision
, last_update_date
, last_updated_by
, creation_date
, created_by
)
VALUES (
l_temp_id
, l_sequence
, l_org_id
, l_item_id
, l_person_id
, SYSDATE
, SYSDATE
, l_loc_id
, -999
, 2
, l_sub
, l_lot
, l_rev
, SYSDATE
, l_last_updated_by
, SYSDATE
, l_last_updated_by
);
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_fm_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
UPDATE mtl_serial_numbers
SET group_mark_id = NULL
WHERE serial_number = l_fm_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
UPDATE mtl_serial_numbers
SET current_status = 5,
previous_status = NULL
WHERE serial_number = l_fm_serial_number
AND inventory_item_id = l_item_id
AND lot_number = l_lot
AND current_status = 4
AND current_organization_id = l_org_id
AND EXISTS (SELECT 1
FROM mtl_txn_request_lines mol
WHERE mol.line_id = l_mmtt_line_id
AND mol.REFERENCE = 'ORDER_LINE_ID');
UPDATE mtl_serial_numbers
SET current_status = 5,
previous_status = NULL
WHERE serial_number = l_fm_serial_number
AND inventory_item_id = l_item_id
AND current_status = 4
AND current_organization_id = l_org_id
AND EXISTS (SELECT 1
FROM mtl_txn_request_lines mol
WHERE mol.line_id = l_mmtt_line_id
AND mol.REFERENCE = 'ORDER_LINE_ID');
l_lpn_update WMS_CONTAINER_PUB.LPN;
SELECT 1
, lpn_id
INTO l_count
, l_to_lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = p_to_lpn
AND wlpn.organization_id = p_org_id;
SELECT quantity
INTO l_quantity
FROM wms_lpn_contents
WHERE parent_lpn_id = l_to_lpn_id
AND ROWNUM < 2;
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE (
NVL(mmtt.subinventory_code, '@') <> NVL(p_sub, NVL(mmtt.subinventory_code, '@'))
OR NVL(mmtt.locator_id, '0') <> NVL(p_loc_id, NVL(mmtt.locator_id, '0'))
)
AND mmtt.organization_id = p_org_id
AND mmtt.transaction_status = 3
AND mmtt.transfer_lpn_id = l_to_lpn_id);
SELECT 1
INTO l_count
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND license_plate_number = p_to_lpn
AND (
wlpn.lpn_context = 5
OR(wlpn.lpn_context = 1
AND wlpn.subinventory_code IS NULL
AND wlpn.locator_id IS NULL)
OR(
wlpn.lpn_context = 1
AND NVL(wlpn.subinventory_code, '@') = NVL(p_sub, NVL(wlpn.subinventory_code, '@'))
AND NVL(wlpn.locator_id, '0') = NVL(p_loc_id, NVL(wlpn.locator_id, '0'))
AND NOT wlpn.license_plate_number = NVL(p_from_lpn, -999)
AND inv_material_status_grp.is_status_applicable(
'TRUE'
, NULL
, inv_globals.g_type_container_pack
, NULL
, NULL
, p_org_id
, NULL
, wlpn.subinventory_code
, wlpn.locator_id
, NULL
, NULL
, 'Z'
) = 'Y'
AND inv_material_status_grp.is_status_applicable(
'TRUE'
, NULL
, inv_globals.g_type_container_pack
, NULL
, NULL
, p_org_id
, NULL
, wlpn.subinventory_code
, wlpn.locator_id
, NULL
, NULL
, 'L'
) = 'Y'
)
));
SELECT COUNT(1)
INTO l_count
FROM mtl_txn_request_lines mol
WHERE mol.lpn_id = l_to_lpn_id
AND mol.line_status <> inv_globals.g_to_status_closed
AND (
(mol.quantity - NVL(mol.quantity_delivered, 0)) > (SELECT SUM(mmtt.transaction_quantity)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol.line_id)
OR(mol.quantity - NVL(mol.quantity_delivered, 0) > 0
AND NOT EXISTS(SELECT 1
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = mol.line_id))
);
SELECT wlc.lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers wlc
WHERE wlc.license_plate_number = p_to_lpn
AND wlc.organization_id = p_org_id;
mydebug('update LPN context to 1');
l_lpn_update.license_plate_number := p_to_lpn;
l_lpn_update.organization_id := p_org_id;
l_lpn_update.lpn_context := wms_container_pub.lpn_context_inv;
, p_lpn => l_lpn_update
) ;
l_lpn_update := NULL;
SELECT lot_number
, serial_transaction_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_temp_id;
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = l_lpn_id
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id
AND NVL(lot_number, -999) = NVL(l_lot_number, -999);
SELECT msi.lot_control_code
, msi.serial_number_control_code
, mmtt.transaction_type_id
, mmtt.inventory_item_id
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_subinventory, subinventory_code) sub
, DECODE(mmtt.transaction_action_id, 2, mmtt.transfer_to_location, locator_id)
, lpn_id
INTO l_lot_control_code
, l_ser_control_code
, l_txn_type_id
, l_item_id
, l_sub
, l_loc
, l_lpn_id
FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = l_org_id
AND mmtt.transaction_temp_id = l_temp_id
AND msi.organization_id = mmtt.organization_id
AND msi.inventory_item_id = mmtt.inventory_item_id;
SELECT moh.header_id
FROM mtl_txn_request_headers moh, mtl_txn_request_lines mol
WHERE moh.move_order_type = inv_globals.g_move_order_put_away
AND moh.header_id = mol.header_id
AND mol.lpn_id = p_lpn_id;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id;
SELECT inventory_item_id
, quantity
, uom_code
, lot_number
, revision
, cost_group_id
, parent_lpn_id
FROM wms_lpn_contents
WHERE parent_lpn_id = l_current_lpn_id;
UPDATE mtl_txn_request_lines mol
SET mol.line_status = inv_globals.g_to_status_closed
WHERE mol.lpn_id = l_lpn_id
AND mol.organization_id = p_org_id
AND mol.quantity_detailed > 0
AND EXISTS
(SELECT 1
FROM
mtl_txn_request_headers moh,
wms_license_plate_numbers wlc
WHERE mol.header_id = moh.header_id
AND moh.move_order_type = inv_globals.g_move_order_put_away
AND wlc.lpn_id = mol.lpn_id
AND wlc.lpn_context = 1);
SELECT subinventory_code
, locator_id
INTO l_m_sub
, l_m_loc
FROM wms_license_plate_numbers
WHERE lpn_id = l_lpn_id;
SELECT mil.project_id
, mil.task_id
INTO l_project_id
, l_task_id
FROM mtl_item_locations mil
WHERE mil.inventory_location_id = l_m_loc
AND mil.organization_id = p_org_id
AND mil.subinventory_code = l_m_sub;
SELECT mtrl.primary_quantity
, mmtt.primary_quantity
FROM mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
WHERE mtrl.organization_id = p_organization_id
AND mtrl.lpn_id = l_lpn_id
AND mmtt.move_order_line_id = mtrl.line_id;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
SELECT 'Y' INTO l_is_content_lpn
FROM wms_lpn_contents
WHERE parent_lpn_id = l_lpn_id;
DELETE FROM wms_dispatched_tasks
WHERE task_type = 2
AND transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id IN
(SELECT line_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id)
);
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id IN
(SELECT transaction_temp_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id IN
(SELECT line_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id));
DELETE FROM mtl_material_transactions_temp
WHERE move_order_line_id IN
(SELECT line_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id);
UPDATE mtl_txn_request_lines
SET to_subinventory_code = p_subinventory
, to_locator_id = p_locator_id
, quantity_detailed = NULL
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id
AND line_status = 7;
SELECT COUNT(1)
INTO l_mo_lines_count
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = l_lpn_id
AND line_status = 7;
SELECT COUNT(mmtt.transaction_temp_id)
INTO l_mmtt_lines_count
FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id = l_lpn_id
AND mtrl.organization_id = p_organization_id
AND NVL(mmtt.wms_task_type, 0) <> -1
AND mtrl.line_id = mmtt.move_order_line_id
AND NVL(mtrl.project_id, -1) = DECODE(mtrl.project_id, NULL, -1, NVL(p_project_id, NVL(mtrl.project_id, -1)))
AND NVL(mtrl.task_id, -1) = DECODE(mtrl.task_id, NULL, -1, NVL(p_task_id, NVL(mtrl.task_id, -1)));
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id;
SELECT mol.lpn_id
, mol.line_id
, mol.inventory_item_id
, mol.revision
, mol.lot_number
, mol.uom_code
, mol.quantity
, mol.primary_quantity
, mol.reference_id
, mol.project_id
, mol.task_id
, mol.txn_source_id
, mol.transaction_type_id
, mol.transaction_source_type_id
, mol.to_cost_group_id
FROM mtl_txn_request_lines mol
WHERE mol.organization_id = p_organization_id
AND mol.header_id IN (SELECT moh.header_id
FROM mtl_txn_request_headers moh
WHERE moh.move_order_type = inv_globals.g_move_order_put_away
)
AND mol.line_status = 7
AND mol.lpn_id = l_current_lpn_id;
SELECT employee_id
INTO l_emp_id
FROM fnd_user
WHERE user_id = p_user_id;
SELECT mmtt.transaction_temp_id,
mmtt.transaction_quantity,
mmtt.transaction_uom,
mmtt.move_order_line_id,
mmtt.secondary_transaction_quantity, --OPM Convergence
mmtt.secondary_uom_code --OPM Convergence
bulk collect
INTO l_mmtt_id_tab,
l_mmtt_qty_tab,
l_mmtt_uom_tab,
l_mmtt_mol_id_tab,
l_mmtt_sec_qty_tab, --OPM Convergence
l_mmtt_sec_uom_tab --OPM Convergence
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mmtt.organization_id = p_organization_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id);
SELECT lpn_context,subinventory_code,locator_id
INTO l_lpn_context,l_subinventory,l_locator_id
FROM wms_license_plate_numbers
WHERE lpn_id = p_lpn_id;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM DUAL;
SELECT DISTINCT operation_seq_num, repetitive_line_id
INTO l_operation_seq_num,l_repetitive_line_id
FROM mtl_material_transactions_temp
WHERE move_order_line_id = v_mo_line.line_id;
-- Insert a record into MMTT
-- Nested LPN changes,
-- For Resides in Receiving LPNs insert destination sub destint loc as sub and loc
-- For Resides in Inventory LPNs insert destination sub and loc as to_sub and to_loc.
mydebug('lpn context =====>' || l_lpn_context );
inv_trx_util_pub.insert_line_trx
(p_trx_hdr_id => l_txn_header_id,
p_item_id => v_mo_line.inventory_item_id,
p_revision => v_mo_line.revision,
p_org_id => p_organization_id,
p_trx_action_id => l_trx_action_id,
p_subinv_code => p_subinventory,
p_locator_id => p_locator_id,
p_trx_type_id => v_mo_line.transaction_type_id,
p_trx_src_type_id => v_mo_line.transaction_source_type_id,
p_trx_qty => v_mo_line.quantity,
p_pri_qty => v_mo_line.primary_quantity,
p_uom => v_mo_line.uom_code,
p_user_id => p_user_id,
p_cost_group => v_mo_line.to_cost_group_id,
p_from_lpn_id => v_mo_line.lpn_id,
p_trx_src_id => v_mo_line.txn_source_id,
x_trx_tmp_id => l_txn_temp_id,
x_proc_msg => x_msg_data,
p_project_id => v_mo_line.project_id,
p_task_id => v_mo_line.task_id,
p_transaction_status => 2);
--BUG 3356366: Insert MMTT with txn_status 2 so that it won't
--invoke the DB trigger that calls update_loc_suggested_capacity
ELSE
l_return :=
inv_trx_util_pub.insert_line_trx
(p_trx_hdr_id => l_txn_header_id,
p_item_id => v_mo_line.inventory_item_id,
p_revision => v_mo_line.revision,
p_org_id => p_organization_id,
p_trx_action_id => l_trx_action_id,
p_subinv_code => nvl(l_subinventory,p_subinventory), -- 4156992
p_locator_id => nvl(l_locator_id,p_locator_id) , -- 4156992
p_trx_type_id => v_mo_line.transaction_type_id,
p_trx_src_type_id => v_mo_line.transaction_source_type_id,
p_trx_qty => v_mo_line.quantity,
p_pri_qty => v_mo_line.primary_quantity,
p_uom => v_mo_line.uom_code,
p_user_id => p_user_id,
p_cost_group => v_mo_line.to_cost_group_id,
p_from_lpn_id => v_mo_line.lpn_id,
p_trx_src_id => v_mo_line.txn_source_id,
x_trx_tmp_id => l_txn_temp_id,
x_proc_msg => x_msg_data,
p_project_id => v_mo_line.project_id,
p_task_id => v_mo_line.task_id,
p_tosubinv_code => p_subinventory,
p_tolocator_id => p_locator_id,
p_transaction_status => 2);
--BUG 3356366: Insert MMTT with txn_status 2 so that it won't
--invoke the DB trigger that calls update_loc_suggested_capacity
END IF;
mydebug('Successfully inserted MMTT record: ' || l_txn_temp_id);
mydebug('Error occurred while calling inv_trx_util_pub.insert_line_trx');
SELECT NVL(completion_transaction_id, -999)
INTO l_completion_txn_id
FROM wip_lpn_completions
WHERE header_id = v_mo_line.reference_id
AND lpn_id = p_lpn_id;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = v_mo_line.inventory_item_id
AND organization_id = p_organization_id;
--Need to update MOL.quantity_detailed
--Because in WMSOPIBB.COMPLETE, it will deduct
--MMTT.TRANSACTION_QUANTITY from MOL.QUANITY_DETIALED
UPDATE mtl_txn_request_lines
SET quantity_detailed = Nvl(quantity_detailed,0)+quantity
WHERE line_id = v_mo_line.line_id;
-- Update the MMTT record with the move order line,
-- completion transaction ID in the case of WIP completion,
-- item primary UOM code, transaction status (2 for suggestions),
-- and wms task type (2 for putaway).
UPDATE mtl_material_transactions_temp
SET move_order_line_id = v_mo_line.line_id
, completion_transaction_id = l_completion_txn_id
, item_primary_uom_code = l_primary_uom_code
, transaction_status = 2
, wms_task_type = 2
, operation_seq_num = l_operation_seq_num --need these 2 columns when
, repetitive_line_id = l_repetitive_line_id --reverting crossdock
WHERE transaction_temp_id = l_txn_temp_id;
mydebug('Updated the MMTT record with additional info');
-- Insert a record into MTLT
IF (l_debug = 1) THEN
mydebug('Insert a record into MTLT for lot: ' || v_mo_line.lot_number);
inv_trx_util_pub.insert_lot_trx
(p_trx_tmp_id => l_txn_temp_id,
p_user_id => p_user_id,
p_lot_number => v_mo_line.lot_number,
p_trx_qty => v_mo_line.quantity,
p_pri_qty => v_mo_line.primary_quantity,
x_ser_trx_id => l_ser_trx_id,
x_proc_msg => x_msg_data);
mydebug('Successfully inserted MTLT record');
mydebug('Error occurred while calling inv_trx_util_pub.insert_lot_trx');
-- Update the MTLT record to clear out the serial_transaction_temp_id column
-- since insert_lot_trx by default will insert a value for it.
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = NULL
WHERE transaction_temp_id = l_txn_temp_id;
DELETE FROM wms_dispatched_tasks
WHERE transaction_temp_id = l_mmtt_id_tab(i)
AND task_type = 2;
DELETE FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = l_mmtt_id_tab(i);
UPDATE mtl_txn_request_lines
SET quantity_detailed = Nvl(quantity_detailed,0)
-Decode(uom_code
,l_mmtt_uom_tab(i)
,l_mmtt_qty_tab(i)
,inv_convert.inv_um_convert
(inventory_item_id
,NULL
,l_mmtt_qty_tab(i)
,l_mmtt_uom_tab(i)
,uom_code
,NULL
,NULL)
)
WHERE line_id = l_mmtt_mol_id_tab(i);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_mmtt_id_tab(i);
mydebug('Finished inserting suggestion records: ' || l_number_of_rows);
SELECT inventory_item_id
, lot_number
, transaction_type_id
FROM mtl_txn_request_lines
WHERE organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = l_item_id
AND current_organization_id = p_organization_id
AND lpn_id = p_lpn_id;
SELECT lot_control_code
, serial_number_control_code
INTO l_lot_control_code
, l_serial_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_item_id
AND organization_id = p_organization_id;
SELECT lpn_id
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY PRIOR lpn_id = parent_lpn_id;
SELECT mmtt.inventory_item_id
--BUG 3541045: For Inventory Move, suggested sub/loc will be stamped
--on transfer_subinventory/transfer_to_location. So, look at that
--first. Only if it is null should we use the locator_id
, Nvl(mmtt.transfer_to_location,mmtt.locator_id) locator_id
, mmtt.transaction_quantity
, mmtt.transaction_uom
, mmtt.parent_line_id --6962664
, mmtt.transaction_header_id --6962664
FROM mtl_material_transactions_temp mmtt,
mtl_txn_request_lines mtrl
WHERE mmtt.move_order_line_id = mtrl.line_id
AND mmtt.organization_id = p_organization_id
AND mmtt.lpn_id = l_current_lpn_id
AND NVL(mmtt.wms_task_type, 0) <> -1;
SELECT locator_id INTO l_locator_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_parent_line_id
AND transaction_header_id = l_txn_header_id;
SELECT 'Y'
INTO x_crossdock
FROM dual
WHERE exists
(SELECT 1
FROM mtl_txn_request_lines
WHERE lpn_id = p_lpn_id
AND organization_id = p_organization_id
AND backorder_delivery_detail_id IS NOT NULL);
SELECT NVL(crossdock_flag, 2)
INTO l_cross_dock_flag
FROM mtl_parameters
WHERE organization_id = p_organization_id;