[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_current_drop_lpn.current_drop_list.DELETE;
g_current_drop_lpn.temp_id_group_ref.DELETE;
g_cur_pend_temp.DELETE;
SELECT mmtt.move_order_line_id
, mmtt.transaction_source_type_id
, mmtt.transaction_action_id
, mmtt.wms_task_type
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_txn_tmp_id;
SELECT mtrh.move_order_type
, mtrl.line_status
FROM mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mtrl.line_id = p_mo_line_id
AND mtrl.header_id = mtrh.header_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd,
mtl_material_transactions_temp mmtt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.move_order_line_id = mmtt.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_tmp_id;
SELECT wda.delivery_id
INTO x_delivery_id
FROM wsh_delivery_assignments_v wda,
wsh_delivery_details_ob_grp_v wdd,
mtl_material_transactions_temp mmtt
WHERE wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.transaction_temp_id = p_temp_id
AND mmtt.transfer_lpn_id = wdd.lpn_id
AND ROWNUM = 1;
SELECT mmtt.parent_line_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT wdt.task_type
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = p_temp_id;
PROCEDURE insert_wdt
( x_return_status OUT NOCOPY VARCHAR2
, p_organization_id IN NUMBER
, p_transfer_lpn_id IN NUMBER
) IS
l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
l_api_name VARCHAR2(30) := 'insert_wdt';
SELECT pmmtt.transaction_temp_id
, wdt.user_task_type
, wdt.person_id
, wdt.effective_start_date
, wdt.effective_end_date
, wdt.equipment_id
, wdt.equipment_instance
, wdt.person_resource_id
, wdt.machine_resource_id
, wdt.dispatched_time
, wdt.last_updated_by
, wdt.created_by
, wdt.task_type
, wdt.loaded_time
FROM mtl_material_transactions_temp pmmtt
, wms_dispatched_tasks wdt
WHERE pmmtt.organization_id = p_org_id
AND pmmtt.transfer_lpn_id = p_lpn_id
AND pmmtt.transaction_temp_id = NVL(pmmtt.parent_line_id,0)
AND wdt.transaction_temp_id = pmmtt.parent_line_id;
SAVEPOINT insert_task_sp;
INSERT INTO wms_dispatched_tasks
( task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
)
( SELECT wms_dispatched_tasks_s.NEXTVAL
, mmtt.transaction_temp_id
, mmtt.organization_id
, c_parent_task_rec.user_task_type
, c_parent_task_rec.person_id
, c_parent_task_rec.effective_start_date
, c_parent_task_rec.effective_end_date
, c_parent_task_rec.equipment_id
, c_parent_task_rec.equipment_instance
, c_parent_task_rec.person_resource_id
, c_parent_task_rec.machine_resource_id
, 4
, c_parent_task_rec.dispatched_time
, SYSDATE
, c_parent_task_rec.last_updated_by
, SYSDATE
, c_parent_task_rec.created_by
, c_parent_task_rec.task_type
, c_parent_task_rec.loaded_time
, mmtt.operation_plan_id
, mmtt.move_order_line_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = c_parent_task_rec.transaction_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND NOT EXISTS
( SELECT 'x'
FROM wms_dispatched_tasks wdt2
WHERE wdt2.transaction_temp_id = mmtt.transaction_temp_id
)
);
( 'No. of WDT records inserted: ' || SQL%ROWCOUNT
, l_api_name
);
ROLLBACK TO insert_task_sp;
END insert_wdt;
SELECT 'x'
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
)
--Added for Bug 6717052
UNION ALL
SELECT 'x'
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id));
SELECT mmtt.transaction_temp_id
, mmtt.transaction_type_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND (mmtt.parent_line_id IS NULL
OR
(mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_ID <> mmtt.transaction_temp_id
)
)
--Added for Bug 6717052
UNION ALL
SELECT mmtt.transaction_temp_id
, mmtt.transaction_type_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id)
AND (mmtt.parent_line_id IS NULL
OR
(mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_ID <> mmtt.transaction_temp_id
)
);
SELECT COUNT (DISTINCT (to_char(mtrh.move_order_type)
||';'||
AND (mmtt.transfer_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id))
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.header_id = mtrh.header_id;
SELECT COUNT (DISTINCT mmtt.transaction_action_id)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
--Added for Bug 6717052
AND (mmtt.transfer_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id))
AND (mmtt.parent_line_id IS NULL
OR
(mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_ID <> mmtt.transaction_temp_id
)
);
SELECT mmtt.transaction_type_id
, mmtt.transaction_source_type_id
, mmtt.transaction_action_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND (mmtt.parent_line_id IS NULL
OR
(mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_ID <> mmtt.transaction_temp_id
)
)
--Added for Bug 6717052
UNION ALL
SELECT mmtt.transaction_type_id
, mmtt.transaction_source_type_id
, mmtt.transaction_action_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id)
AND (mmtt.parent_line_id IS NULL
OR
(mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_ID <> mmtt.transaction_temp_id
)
);
SELECT COUNT (DISTINCT( mmtt.transfer_subinventory
||';'||
AND (mmtt.transfer_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id IN (SELECT wlpn.lpn_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.outermost_lpn_id = p_lpn_id))
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mtrl.line_id = mmtt.move_order_line_id
AND wdd.organization_id = mtrl.organization_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status NOT IN ('Y','C','D','I','L','P');--Bug 13644027 [Y-picked,C-shipped,D-cancelled,I-interfaced,L-closed,P-purged]
SELECT Count(DISTINCT wda.delivery_id)
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mtrl.line_id = mmtt.move_order_line_id
AND wdd.organization_id = mtrl.organization_id
AND wdd.move_order_line_id = mtrl.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.released_status NOT IN ('Y','C','D','I','L','P');
SELECT DISTINCT delivery_detail_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wsh_delivery_details wdd
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mtrl.line_id = mmtt.move_order_line_id
AND wdd.organization_id = mtrl.organization_id
AND wdd.move_order_line_id = mtrl.line_id
AND wdd.released_status NOT IN ('Y', 'C'); --Bug 5768776
SELECT COUNT (DISTINCT (to_char(mtrl.txn_source_id)
||';'||
SELECT Decode (Count(DISTINCT transfer_subinventory||' '||transfer_to_location),1,'N','Y')
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND ((mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
) OR
mmtt.parent_line_id IS NULL
);
SELECT COUNT (DISTINCT (mmtt.transfer_subinventory
||';'||
insert_wdt
( x_return_status => l_api_return_status
, p_organization_id => p_organization_id
, p_transfer_lpn_id => p_transfer_lpn_id
);
( 'Error from insert_wdt'
, l_api_name
);
wms_op_runtime_pub_apis.update_drop_locator_for_task
( x_return_status => l_api_return_status
, x_message => l_message
, x_drop_lpn_option => l_drop_lpn_option
, p_transfer_lpn_id => p_transfer_lpn_id
);
( 'Return status from wms_op_runtime_pub_apis.update_drop_locator_for_task: ' ||
g_newline || 'l_api_return_status: ' || l_api_return_status ||
g_newline || 'l_message: ' || l_message ||
g_newline || 'l_drop_lpn_option: ' || to_char(l_drop_lpn_option)
, l_api_name
);
print_debug('Error status from update_drop_locator_for_task: ' || l_api_return_status
, l_api_name);
print_debug('update_drop_locator_for_task returned success', l_api_name);
g_current_drop_lpn.current_drop_list.DELETE;
g_current_drop_lpn.temp_id_group_ref.DELETE;
SELECT mmtt.lpn_id
, mmtt.content_lpn_id
, mmtt.transfer_lpn_id
, mmtt.inventory_item_id
, mmtt.revision
, mmtt.primary_quantity
, mmtt.parent_line_id
, mmtt.serial_allocated_flag
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT msik.concatenated_segments item_num
, msik.lot_control_code lot_control
, msik.serial_number_control_code serial_control
, msik.primary_uom_code primary_uom_code
, NVL(msik.allowed_units_lookup_code,2) uom_lookup_code
FROM mtl_system_items_kfv msik
WHERE msik.organization_id = p_org_id
AND msik.inventory_item_id = p_item_id;
SELECT license_plate_number
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id;
SELECT 'x'
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id = p_lpn_id
);
SELECT mtlt.lot_number
, mtlt.primary_quantity
, mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id;
SELECT msnt.fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id;
DELETE mtl_allocations_gtmp;
UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = c_mmtt_rec.lpn_id;
( 'Updated LPN context back to 1 for LPN ' || l_inner_lpn
, l_api_name
);
( 'Inserting content LPN ' || l_content_lpn
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id --9593852
, NULL
, l_cur_group_num
, l_content_lpn
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
);
( 'Unexpected error inserting content LPN: ' || sqlerrm
, l_api_name
);
SELECT group_number
INTO l_group_num
FROM mtl_allocations_gtmp mtg
WHERE mtg.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mtg.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND NVL(mtg.inner_lpn,l_dum_lpn)
= NVL(l_content_lpn, NVL(l_inner_lpn,l_dum_lpn))
AND mtg.content_lpn IS NULL;
UPDATE mtl_allocations_gtmp
SET primary_quantity
= primary_quantity + c_mmtt_rec.primary_quantity
WHERE group_number = l_group_num;
( 'Inserting item ' || c_item_rec.item_num ||
', revision ' || c_mmtt_rec.revision ||
' with primary qty ' || to_char(c_mmtt_rec.primary_quantity) ||
' and primary uom ' || c_item_rec.primary_uom_code
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id
, c_item_rec.item_num
, l_cur_group_num
, NULL
, NVL(l_content_lpn, l_inner_lpn)
, c_mmtt_rec.revision
, NULL
, NULL
, l_lot_alloc
, l_serial_alloc
, c_mmtt_rec.primary_quantity
, c_item_rec.primary_uom_code
, c_item_rec.uom_lookup_code
);
SELECT group_number
INTO l_group_num
FROM mtl_allocations_gtmp mtg
WHERE mtg.inventory_item_id = c_mmtt_rec.inventory_item_id
AND mtg.lot_number = c_lot_dtl_rec.lot_number
AND NVL(mtg.inner_lpn, l_dum_lpn)
= NVL(l_inner_lpn, l_dum_lpn)
AND NVL(mtg.revision,'@@@@')
= NVL(c_mmtt_rec.revision,'@@@@')
AND mtg.content_lpn IS NULL
AND rownum < 2 ; --Bug#12853197
UPDATE mtl_allocations_gtmp
SET primary_quantity
= primary_quantity + c_lot_dtl_rec.primary_quantity
WHERE group_number = l_group_num;
( 'Inserting item ' || c_item_rec.item_num ||
', group number ' || to_char(l_group_num) ||
', revision ' || c_mmtt_rec.revision ||
', lot number ' || c_lot_dtl_rec.lot_number ||
', primary qty ' || to_char(c_lot_dtl_rec.primary_quantity) ||
', primary uom code ' || c_item_rec.primary_uom_code
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, loose_qty_exists
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, transaction_quantity
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id
, c_item_rec.item_num
, l_group_num
, NULL
, l_inner_lpn
, 'Y'
, c_mmtt_rec.revision
, c_lot_dtl_rec.lot_number
, NULL
, l_lot_alloc
, l_serial_alloc
, NULL
, c_mmtt_rec.primary_quantity
, c_item_rec.primary_uom_code
, c_item_rec.uom_lookup_code
);
( 'Inserting item ' || c_item_rec.item_num ||
', group # ' || to_char(l_group_num) ||
', lot number ' || c_lot_dtl_rec.lot_number ||
', serial # ' || c_srl_lot_dtl_rec.fm_serial_number
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, loose_qty_exists
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, transaction_quantity
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id
, c_item_rec.item_num
, l_group_num
, NULL
, l_inner_lpn
, 'Y'
, c_mmtt_rec.revision
, c_lot_dtl_rec.lot_number
, c_srl_lot_dtl_rec.fm_serial_number
, l_lot_alloc
, l_serial_alloc
, NULL
, 1
, c_item_rec.primary_uom_code
, c_item_rec.uom_lookup_code
);
( 'Unexpected error inserting allocated serials: ' || sqlerrm
, l_api_name
);
SELECT group_number
INTO l_group_num
FROM mtl_allocations_gtmp mtg
WHERE mtg.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mtg.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND NVL(mtg.inner_lpn, l_dum_lpn)
= NVL(l_inner_lpn, l_dum_lpn)
AND mtg.content_lpn IS NULL
AND ROWNUM<2; --Bug#12853197
( 'Inserting item ' || c_item_rec.item_num ||
', serial # ' || c_srl_numbers_rec.fm_serial_number
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, loose_qty_exists
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, transaction_quantity
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id
, c_item_rec.item_num
, l_group_num
, NULL
, l_inner_lpn
, 'Y'
, c_mmtt_rec.revision
, NULL
, c_srl_numbers_rec.fm_serial_number
, l_lot_alloc
, l_serial_alloc
, NULL
, 1
, c_item_rec.primary_uom_code
, c_item_rec.uom_lookup_code
);
( 'Unexpected error inserting allocated serials: ' || sqlerrm
, l_api_name
);
UPDATE mtl_material_transactions_temp mmtt
SET lpn_id = NULL
, content_lpn_id = NULL
WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
SELECT group_number
INTO l_group_num
FROM mtl_allocations_gtmp mtg
WHERE mtg.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mtg.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND mtg.inner_lpn IS NULL
AND mtg.content_lpn IS NULL;
UPDATE mtl_allocations_gtmp
SET primary_quantity
= primary_quantity + c_mmtt_rec.primary_quantity
WHERE group_number = l_group_num;
( 'Inserting item ' || c_item_rec.item_num ||
', revision ' || c_mmtt_rec.revision ||
' with primary qty ' || to_char(c_mmtt_rec.primary_quantity) ||
', primary uom code ' || c_item_rec.primary_uom_code
, l_api_name
);
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id <> mmtt.transfer_lpn_id
);
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
)
);
SELECT mmtt.content_lpn_id INTO l_inner_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id <> mmtt.transfer_lpn_id ;
UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id IN
( SELECT mmtt.content_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id <> mmtt.transfer_lpn_id
AND mmtt.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
);
( 'Updated LPN context back to 1 for nested LPN(s)'
, l_api_name
);
UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id IN
( SELECT mmtt.content_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id = mmtt.transfer_lpn_id
AND mmtt.inventory_item_id = c_mmtt_rec.inventory_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(c_mmtt_rec.revision,'@@@@')
);
( 'Updated LPN context back to 1 for content/transfer LPN'
, l_api_name
);
INSERT INTO mtl_allocations_gtmp
( inventory_item_id
, item_number
, group_number
, content_lpn
, inner_lpn
, inner_lpn_exists
, loose_qty_exists
, revision
, lot_number
, serial_number
, lot_alloc
, serial_alloc
, transaction_quantity
, primary_quantity
, primary_uom_code
, uom_lookup_code
)
VALUES
( c_mmtt_rec.inventory_item_id
, c_item_rec.item_num
, l_cur_group_num
, NULL
, l_inner_lpn --Bug6913674
, l_inner_lpn_exists
, l_loose_exists
, c_mmtt_rec.revision
, NULL
, NULL
, l_lot_alloc
, l_serial_alloc
, NULL
, c_mmtt_rec.primary_quantity
, c_item_rec.primary_uom_code
, c_item_rec.uom_lookup_code
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_new_temp_id
FROM dual;
( 'About to insert temp ID ' || to_char(l_new_temp_id)
, l_api_name
);
INSERT INTO mtl_material_transactions_temp
( transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, error_code
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, parent_line_id
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, operation_plan_id
, move_order_header_id
, serial_allocated_flag
)
( SELECT transaction_header_id
, l_new_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, error_code
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, parent_line_id
, transfer_cost_group_id
, NULL
, transfer_lpn_id
, NULL
, operation_plan_id
, move_order_header_id
, serial_allocated_flag
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id
);
print_debug( 'About to insert WDT record', l_api_name);
INSERT INTO wms_dispatched_tasks
( task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
)
( SELECT wms_dispatched_tasks_s.NEXTVAL
, l_new_temp_id
, mmtt.organization_id
, wdt.user_task_type
, wdt.person_id
, wdt.effective_start_date
, wdt.effective_end_date
, wdt.equipment_id
, wdt.equipment_instance
, wdt.person_resource_id
, wdt.machine_resource_id
, 4
, wdt.dispatched_time
, SYSDATE
, wdt.last_updated_by
, SYSDATE
, wdt.created_by
, wdt.task_type
, wdt.loaded_time
, mmtt.operation_plan_id
, mmtt.move_order_line_id
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
);
x_temp_tbl.DELETE;
l_mmtt_cur_str :='SELECT mmtt.TRANSACTION_TEMP_ID FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt '||
' WHERE mmtt.TRANSACTION_TEMP_ID=wdt.TRANSACTION_TEMP_ID(+) and mmtt.TRANSACTION_TEMP_ID in ('||l_temp_ids||') ORDER BY wdt.loaded_time';
SELECT COUNT(*)
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
SELECT mtlt.rowid
, mtlt.lot_number
, mtlt.transaction_quantity
, mtlt.primary_quantity
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity
- lot_dtl_rec.transaction_quantity
, primary_quantity = primary_quantity
- lot_dtl_rec.primary_quantity
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity
- lot_dtl_rec.primary_quantity
)
WHERE transaction_temp_id = l_orig_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = lot_dtl_rec.transaction_quantity
, primary_quantity = lot_dtl_rec.primary_quantity
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, lot_dtl_rec.primary_quantity
)
WHERE transaction_temp_id = l_transaction_temp_id;
('Updated new temp ID ' || to_char(l_transaction_temp_id)
, l_api_name
);
UPDATE mtl_transaction_lots_temp
SET transaction_temp_id = l_transaction_temp_id
WHERE rowid = lot_dtl_rec.rowid;
( 'Updated MTLT record for lot ' || lot_dtl_rec.lot_number
|| ' to new temp ID ' || to_char(l_transaction_temp_id)
, l_api_name
);
l_temp_tbl.DELETE;
SELECT 1 line_type
, NVL(msi.dropping_order, 0) sub_dropping_order
, NVL(msi.picking_order, 0) sub_picking_order
, mmtt.transfer_subinventory transfer_subinventory
, NVL(mil.dropping_order, 0) loc_dropping_order
, NVL(mil.picking_order, 0) loc_picking_order
, mmtt.transfer_to_location transfer_to_location
, null loaded_time -- Added for bug 12853197
, NVL(wda.delivery_id,-1)-- bug#10062741 NVL(wda.delivery_id,NVL(mtrl.carton_grouping_id,0))
delivery_id
,wdd.delivery_detail_id delivery_detail_id --Bug 10062741
, 0 txn_source_id
, 0 txn_source_line_id
, 0 reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
, mtl_item_locations mil
, mtl_txn_request_lines mtrl
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 28
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mmtt.organization_id = msi.organization_id
AND mmtt.transfer_subinventory = msi.secondary_inventory_name
AND mmtt.organization_id = mil.organization_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status <> 9
AND mtrl.organization_id = wdd.organization_id
AND mtrl.line_id = wdd.move_order_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
UNION ALL
-- Non-WIP sub xfers, not in cancelled status
SELECT 2 line_type
, NVL(msi.dropping_order, 0) sub_dropping_order
, NVL(msi.picking_order, 0) sub_picking_order
, mmtt.transfer_subinventory transfer_subinventory
, NVL(mil.dropping_order, 0) loc_dropping_order
, NVL(mil.picking_order, 0) loc_picking_order
, mmtt.transfer_to_location transfer_to_location
, max(wdt.loaded_time) over (PARTITION BY mmtt.transfer_subinventory, mmtt.transfer_to_location, mmtt.inventory_item_id, mmtt.revision) loaded_time --Bug#12853197
, 0 delivery_id
, 0 delivery_detail_id --Bug 10062741
, 0 txn_source_id
, 0 txn_source_line_id
, 0 reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
, mtl_item_locations mil
, mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
, wms_dispatched_tasks wdt -- Added for bug 12853197
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 2
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mmtt.organization_id = msi.organization_id
AND mmtt.transfer_subinventory = msi.secondary_inventory_name
AND mmtt.organization_id = mil.organization_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status <> 9
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type <> 5
AND wdt.transaction_temp_id(+) = mmtt.transaction_temp_id -- Added for bug 12853197
UNION ALL
-- WIP sub xfers, not in cancelled status
SELECT 3 line_type
, NVL(msi.dropping_order, 0) sub_dropping_order
, NVL(msi.picking_order, 0) sub_picking_order
, mmtt.transfer_subinventory transfer_subinventory
, NVL(mil.dropping_order, 0) loc_dropping_order
, NVL(mil.picking_order, 0) loc_picking_order
, mmtt.transfer_to_location transfer_to_location
, null loaded_time -- Added for bug 12853197
, 0 delivery_id
, 0 delivery_detail_id --Bug 10062741
, 0 txn_source_id
, 0 txn_source_line_id
, 0 reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id inventory_item_id
, mmtt.revision revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_secondary_inventories msi
, mtl_item_locations mil
, mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 2
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mmtt.organization_id = msi.organization_id
AND mmtt.transfer_subinventory = msi.secondary_inventory_name
AND mmtt.organization_id = mil.organization_id
AND mmtt.transfer_subinventory = mil.subinventory_code
AND mmtt.transfer_to_location = mil.inventory_location_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status <> 9
AND mtrl.header_id = mtrh.header_id
AND mtrh.move_order_type = 5
UNION ALL
-- WIP issues, not in cancelled status
SELECT 4 line_type
, 0 sub_dropping_order
, 0 sub_picking_order
, to_char(NULL) transfer_subinventory
, 0 loc_dropping_order
, 0 loc_picking_order
, 0 transfer_to_location
, null loaded_time -- Added for bug 12853197
, 0 delivery_id
, 0 delivery_detail_id --Bug 10062741
, mtrl.txn_source_id
, mtrl.txn_source_line_id
, mtrl.reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 1
AND mmtt.transaction_source_type_id = 5
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_status <> 9
UNION ALL
-- Cancelled lines
SELECT 5 line_type
, 0 sub_dropping_order
, 0 sub_picking_order
, to_char(NULL) transfer_subinventory
, 0 loc_dropping_order
, 0 loc_picking_order
, 0 transfer_to_location
, null loaded_time -- Added for bug 12853197
, 0 delivery_id
, 0 delivery_detail_id --Bug 10062741
, 0 txn_source_id
, 0 txn_source_line_id
, 0 reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id IN (2,28)
AND mmtt.move_order_line_id = mtrl.line_id
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
AND mtrl.line_status = 9
UNION ALL
-- Overpicked lines, other sub xfers without move orders
SELECT 6 line_type
, 0 sub_dropping_order
, 0 sub_picking_order
, to_char(NULL) transfer_subinventory
, 0 loc_dropping_order
, 0 loc_picking_order
, 0 transfer_to_location
, null loaded_time -- Added for bug 12853197
, 0 delivery_id
, 0 delivery_detail_id --Bug 10062741
, 0 txn_source_id
, 0 txn_source_line_id
, 0 reference_id
, mmtt.transaction_temp_id
, mmtt.inventory_item_id
, mmtt.revision
, DECODE( mmtt.parent_line_id
, NULL, 0
, 1
) parent_line_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.transaction_action_id = 2
AND mmtt.move_order_line_id IS NULL
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
)
ORDER BY 1,2,3,4,5,6,7,8 desc,9,10,11,13,14,15; -- Modified for bug 12853197
SELECT mmtt.transaction_type_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
g_current_drop_lpn.current_drop_list.DELETE;
g_current_drop_lpn.temp_id_group_ref.DELETE;
SELECT count(*)
INTO l_count
FROM mtl_allocations_gtmp;
SELECT mag.inventory_item_id
, mag.item_number
, mag.group_number
, mag.content_lpn
, mag.inner_lpn
, mag.inner_lpn_exists
, mag.loose_qty_exists
, mag.revision
, mag.lot_number
, mag.serial_number
, mag.lot_alloc
, mag.serial_alloc
, mag.primary_quantity
, mag.primary_uom_code
, mag.uom_lookup_code
, msi.description
FROM mtl_allocations_gtmp mag, mtl_system_items_vl msi
WHERE mag.inventory_item_id = msi.inventory_item_id
AND msi.ORGANIZATION_ID = p_organization_id
ORDER BY group_number;
SELECT Sum(primary_quantity)
INTO g_total_qty
FROM mtl_allocations_gtmp;
SELECT wip_entity_type
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wip_entities we
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.transaction_source_type_id = 5
AND mmtt.transaction_action_id = 1
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.txn_source_id = we.wip_entity_id
AND mtrl.organization_id = we.organization_id;
SELECT mmtt.transfer_subinventory, mmtt.transfer_to_location,
decode(transaction_action_id, 28,mmtt.cartonization_id, NULL)
--mmtt.cartonization_id has the LPN suggested by MDC
--IT is applicable only to MDC case for staging transfer PickDrops
-- For all others, it should be null
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT license_plate_number
INTO x_transfer_lpn
FROM wms_license_plate_numbers
WHERE lpn_id = x_transfer_lpn_id;
SELECT wlpn.outermost_lpn_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, wms_license_plate_numbers wlpn
WHERE wda.delivery_id = p_deliv_id
AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = p_org_id
AND wdd.lpn_id = wlpn.lpn_id
AND wlpn.subinventory_code = p_subinv
AND wlpn.locator_id = p_loc_id
AND wlpn.lpn_context = 11
ORDER BY wda.CREATION_DATE DESC;
SELECT license_plate_number
FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_id = p_lpn_id;
SELECT lot_number
, primary_quantity
FROM mtl_allocations_gtmp
WHERE inventory_item_id = p_item_id
AND NVL(revision,'@@@@') = NVL(p_revision, '@@@@')
AND NVL(inner_lpn,l_dum_lpn) = NVL(p_inner_lpn,l_dum_lpn)
AND lot_number LIKE (p_lot_num);
SELECT lot_number
, inv_convert.inv_um_convert
( p_item_id
, NULL
, primary_quantity
, primary_uom_code
, p_conf_uom_code
, NULL
, NULL
) lot_qty
FROM mtl_allocations_gtmp
WHERE inventory_item_id = p_item_id
AND NVL(revision,'@@@@') = NVL(p_revision, '@@@@')
AND NVL(inner_lpn,l_dum_lpn) = NVL(p_inner_lpn,l_dum_lpn)
AND lot_number LIKE (p_lot_num);
SELECT serial_number
FROM mtl_allocations_gtmp
WHERE inventory_item_id = p_item_id
AND NVL(revision,'@@@@') = NVL(p_revision, '@@@@')
AND NVL(inner_lpn,l_dum_lpn) = NVL(p_inner_lpn,l_dum_lpn)
AND NVL(lot_number,'@@@@') = NVL(p_lot_num, '@@@@')
AND serial_number LIKE (p_serial);
PROCEDURE insert_child_msnt
( x_return_status OUT NOCOPY VARCHAR2
, p_temp_id IN NUMBER
, p_parent_tmp_id IN NUMBER
, p_txn_header_id IN NUMBER
) IS
l_api_name VARCHAR2(30) := 'insert_child_msnt';
SELECT mmtt.primary_quantity
, mmtt.inventory_item_id
, mmtt.organization_id
, mmtt.revision
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_tmp_id;
SELECT rowid
, fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id = p_parent_id;
SELECT msnt.rowid
, msnt.fm_serial_number
FROM mtl_transaction_lots_temp mtlt
, mtl_serial_numbers_temp msnt
WHERE mtlt.transaction_temp_id = p_parent_id
AND mtlt.lot_number = p_lot_num
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT mtlt.rowid
, mtlt.lot_number
, mtlt.primary_quantity
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_tmp_id;
SAVEPOINT insert_msnt_sp;
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
);
UPDATE mtl_serial_numbers_temp msnt
SET transaction_temp_id = p_temp_id
WHERE rowid = msnt_rec.rowid;
( 'Updated serial ' || msnt_rec.fm_serial_number ||
', l_srl_qty is now: ' || to_char(l_srl_qty)
, l_api_name
);
UPDATE mtl_serial_numbers
SET group_mark_id = p_txn_header_id
WHERE current_organization_id = mmtt_info_rec.organization_id
AND inventory_item_id = mmtt_info_rec.inventory_item_id
AND NVL(revision,'@@@@') = NVL(mmtt_info_rec.revision,'@@@@')
AND serial_number = msnt_rec.fm_serial_number;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_serial_temp_id
FROM dual;
UPDATE mtl_transaction_lots_temp
SET serial_transaction_temp_id = l_serial_temp_id
WHERE rowid = lot_details_rec.rowid;
UPDATE mtl_serial_numbers_temp msnt
SET transaction_temp_id = l_serial_temp_id
WHERE rowid = lot_msnt_rec.rowid;
( 'Updated serial ' || lot_msnt_rec.fm_serial_number ||
', l_srl_qty is now: ' || to_char(l_srl_qty)
, l_api_name
);
UPDATE mtl_serial_numbers
SET group_mark_id = p_txn_header_id
WHERE current_organization_id = mmtt_info_rec.organization_id
AND inventory_item_id = mmtt_info_rec.inventory_item_id
AND NVL(revision,'@@@@') = NVL(mmtt_info_rec.revision,'@@@@')
AND serial_number = lot_msnt_rec.fm_serial_number;
ROLLBACK TO insert_msnt_sp;
END insert_child_msnt;
SELECT wlpn.lpn_id
, mmtt.transaction_temp_id
, mmtt.primary_quantity
, mmtt.subinventory_code
, mmtt.locator_id
FROM wms_license_plate_numbers wlpn
, mtl_material_transactions_temp mmtt
WHERE wlpn.license_plate_number = p_inner_lpn
AND wlpn.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id <> mmtt.transfer_lpn_id
AND mmtt.content_lpn_id = wlpn.lpn_id;
UPDATE mtl_material_transactions_temp mmtt
SET lpn_id = l_inner_lpn_id
, transfer_lpn_id = DECODE( x_ret_code
, 2, l_inner_lpn_id
, NULL
)
, subinventory_code = l_from_sub
, locator_id = l_from_loc_id
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING mmtt.primary_quantity
, mmtt.transaction_header_id
INTO l_mmtt_qty
, l_txn_header_id;
( 'Updated temp ID ' || to_char(l_transaction_temp_id) ||
' having qty ' || to_char(l_mmtt_qty) ||
' and txn hdr ID ' || to_char(l_txn_header_id) ||
'. Total qty is ' || to_char(l_tot_mmtt_qty)
, l_api_name
);
UPDATE wms_license_plate_numbers
SET lpn_context = WMS_Container_PUB.LPN_CONTEXT_PACKING
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE lpn_id = l_inner_lpn_id;
print_debug ('Updated LPN context to pack since entire LPN is
consumed for LPN ' || l_inner_lpn_id
, l_api_name);
insert_child_msnt
( x_return_status => l_api_return_status
, p_temp_id => l_transaction_temp_id
, p_parent_tmp_id => l_parent_temp_id
, p_txn_header_id => l_txn_header_id
);
print_debug ('Error from insert_child_msnt', l_api_name);
print_debug ('Success status from insert_child_msnt', l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET lpn_id = l_inner_lpn_id
, transfer_lpn_id = l_inner_lpn_id
, subinventory_code = l_from_sub
, locator_id = l_from_loc_id
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING mmtt.primary_quantity
, mmtt.transaction_header_id
INTO l_mmtt_qty
, l_txn_header_id;
( ' Updated temp ID ' || to_char(l_transaction_temp_id)
|| ', primary qty is ' || to_char(l_mmtt_qty)
|| ', txn hdr ID is ' || to_char(l_txn_header_id)
, l_api_name
);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = DECODE( transaction_uom
, p_primary_uom, l_remaining_qty
, inv_convert.inv_um_convert
( p_item_id
, NULL
, l_remaining_qty
, p_primary_uom
, transaction_uom
, NULL
, NULL
)
)
, primary_quantity = l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, l_remaining_qty
)
WHERE transaction_temp_id = l_transaction_temp_id
RETURNING transaction_quantity INTO l_txn_qty;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_txn_qty
, primary_quantity = primary_quantity - l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - l_remaining_qty
)
, lpn_id = NULL
, transfer_lpn_id = NULL
, subinventory_code = NULL
, locator_id = NULL
WHERE transaction_temp_id = l_new_temp_id
RETURNING primary_quantity INTO l_pri_qty;
('Updated new temp ID ' || to_char(l_new_temp_id) ||
' with qty : ' || to_char(l_pri_qty)
, l_api_name
);
insert_child_msnt
( x_return_status => l_api_return_status
, p_temp_id => l_transaction_temp_id
, p_parent_tmp_id => l_parent_temp_id
, p_txn_header_id => l_txn_header_id
);
print_debug ('Error from insert_child_msnt', l_api_name);
print_debug ('Success status from insert_child_msnt', l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET content_lpn_id = NULL
, transaction_quantity = 0
, primary_quantity = 0
, reservation_quantity = 0
WHERE mmtt.transaction_temp_id = l_parent_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = DECODE( transaction_uom
, p_primary_uom, (primary_quantity - l_tot_mmtt_qty)
, inv_convert.inv_um_convert
( p_item_id
, NULL
, primary_quantity - l_tot_mmtt_qty
, p_primary_uom
, transaction_uom
, NULL
, NULL
)
)
, primary_quantity = primary_quantity - l_tot_mmtt_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - l_tot_mmtt_qty
)
WHERE mmtt.transaction_temp_id = l_parent_temp_id;
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = p_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(p_revision,'@@@@')
AND mmtt.content_lpn_id IS NOT NULL
AND mmtt.content_lpn_id <> mmtt.transfer_lpn_id
);
l_temp_tbl.DELETE;
SELECT NVL(SUM(mmtt.primary_quantity),0)
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = p_itm_id
AND NVL(mmtt.revision,'@@@@') = NVL(p_rev,'@@@@')
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
);
SELECT primary_quantity
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = p_itm_id
AND NVL(mmtt.revision,'@@@@') = NVL(p_rev,'@@@@')
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
);
SELECT mmtt.lpn_id
, mmtt.content_lpn_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.primary_quantity
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = DECODE( transaction_uom
, p_primary_uom, l_remaining_qty
, inv_convert.inv_um_convert
( p_item_id
, NULL
, l_remaining_qty
, p_primary_uom
, transaction_uom
, NULL
, NULL
)
)
, primary_quantity = l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, l_remaining_qty
)
WHERE transaction_temp_id = l_transaction_temp_id
RETURNING transaction_quantity INTO l_txn_qty;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_txn_qty
, primary_quantity = primary_quantity - l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - l_remaining_qty
)
, lpn_id = NULL
, transfer_lpn_id = NULL
, subinventory_code = NULL
, locator_id = NULL
WHERE transaction_temp_id = l_new_temp_id
RETURNING primary_quantity INTO l_pri_qty;
('Updated new temp ID ' || to_char(l_new_temp_id) ||
' with qty : ' || to_char(l_pri_qty)
, l_api_name
);
l_temp_tbl.DELETE;
UPDATE mtl_material_transactions_temp mmtt
SET lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
, subinventory_code = parent_rec.subinventory_code
, locator_id = parent_rec.locator_id
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING mmtt.primary_quantity
INTO l_mmtt_qty;
( ' Updated temp ID ' || to_char(l_transaction_temp_id)
|| ', primary qty is ' || to_char(l_mmtt_qty)
, l_api_name
);
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = DECODE( transaction_uom
, p_primary_uom, (primary_quantity - l_mmtt_qty)
, inv_convert.inv_um_convert
( p_item_id
, NULL
, primary_quantity - l_mmtt_qty
, p_primary_uom
, transaction_uom
, NULL
, NULL
)
)
, primary_quantity = primary_quantity - l_mmtt_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - l_mmtt_qty
)
WHERE mmtt.transaction_temp_id = l_parent_temp_id;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = DECODE( transaction_uom
, p_primary_uom, l_remaining_qty
, inv_convert.inv_um_convert
( p_item_id
, NULL
, l_remaining_qty
, p_primary_uom
, transaction_uom
, NULL
, NULL
)
)
, primary_quantity = l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, l_remaining_qty
)
WHERE transaction_temp_id = l_transaction_temp_id
RETURNING transaction_quantity INTO l_txn_qty;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - l_txn_qty
, primary_quantity = primary_quantity - l_remaining_qty
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - l_remaining_qty
)
, lpn_id = NULL
, transfer_lpn_id = NULL
, subinventory_code = NULL
, locator_id = NULL
WHERE transaction_temp_id = l_new_temp_id
RETURNING primary_quantity INTO l_pri_qty;
('Updated new temp ID ' || to_char(l_new_temp_id) ||
' with qty : ' || to_char(l_pri_qty)
, l_api_name
);
UPDATE mtl_material_transactions_temp mmtt
SET transaction_quantity = 0
, primary_quantity = 0
, reservation_quantity = 0
WHERE mmtt.transaction_temp_id = l_parent_temp_id;
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = p_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(p_revision,'@@@@')
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
)
);
l_process_tbl.DELETE;
l_parent_tbl.DELETE;
SELECT wlpn.lpn_id
FROM wms_license_plate_numbers wlpn
, mtl_material_transactions_temp mmtt
WHERE wlpn.license_plate_number = p_inner_lpn
AND wlpn.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_outer_lpn_id
AND mmtt.organization_id = p_org_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id = wlpn.lpn_id;
SELECT msnt.rowid
, mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
, mtl_serial_numbers_temp msnt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_xfer_lpn_id
AND mmtt.transaction_temp_id = mmtt.parent_line_id
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
)
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number = p_serial_num;
SELECT msnt.rowid
, mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
, mtl_serial_numbers_temp msnt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_xfer_lpn_id
AND mmtt.transaction_temp_id = mmtt.parent_line_id
AND mmtt.content_lpn_id = p_lpn_id
AND mmtt.transaction_temp_id = msnt.transaction_temp_id
AND msnt.fm_serial_number = p_serial_num;
SELECT msnt.rowid
, mmtt.transaction_temp_id transaction_temp_id
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
, mtl_serial_numbers_temp msnt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_xfer_lpn_id
AND mmtt.transaction_temp_id = mmtt.parent_line_id
AND NVL(mmtt.content_lpn_id,0) = NVL(p_lpn_id,0)
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_num
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number = p_serial_num;
SELECT parent_line_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT mmtt.lpn_id
, mmtt.content_lpn_id
, mmtt.subinventory_code
, mmtt.locator_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT mtlt.primary_quantity
, COUNT(msnt.fm_serial_number) srl_count
FROM mtl_material_transactions_temp mmtt
, mtl_transaction_lots_temp mtlt
, mtl_serial_numbers_temp msnt
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
AND mtlt.lot_number = p_lot_num
AND msnt.transaction_temp_id (+) = mtlt.serial_transaction_temp_id
GROUP BY mtlt.primary_quantity;
SELECT mmtt.primary_quantity
, COUNT(msnt.fm_serial_number) srl_count
FROM mtl_material_transactions_temp mmtt
, mtl_serial_numbers_temp msnt
WHERE mmtt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id (+) = mmtt.transaction_temp_id
GROUP BY mmtt.primary_quantity;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - 1
, primary_quantity = primary_quantity - 1
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, reservation_quantity - 1
)
WHERE transaction_temp_id = l_orig_temp_id;
SELECT mtlt.rowid
INTO l_old_mtlt_rowid
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_orig_temp_id
AND mtlt.lot_number = p_lot_number
AND rownum < 2;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity - 1
, primary_quantity = primary_quantity - 1
WHERE rowid = l_old_mtlt_rowid;
UPDATE mtl_material_transactions_temp
SET transaction_quantity = 1
, primary_quantity = 1
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, 1
)
, parent_line_id = lot_msnt_rec.transaction_temp_id
, subinventory_code = parent_rec.subinventory_code
, locator_id = parent_rec.locator_id
, lpn_id = NVL( l_inner_lpn_id
, NVL( parent_rec.content_lpn_id
, parent_rec.lpn_id
)
)
WHERE transaction_temp_id = l_transaction_temp_id;
('Updated new temp ID ' || to_char(l_transaction_temp_id) ||
' with qty 1, sub ' || parent_rec.subinventory_code ||
', locator ID: ' || to_char(parent_rec.locator_id) ||
', LPN ID: ' || to_char( NVL( l_inner_lpn_id
, NVL( parent_rec.content_lpn_id
, parent_rec.lpn_id
)
)
) ||
', parent line ID: ' || to_char(lot_msnt_rec.transaction_temp_id)
, l_api_name
);
SELECT *
INTO l_mtlt_rec
FROM mtl_transaction_lots_temp
WHERE rowid = l_old_mtlt_rowid;
INSERT INTO mtl_transaction_lots_temp
( TRANSACTION_TEMP_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, TRANSACTION_QUANTITY
, PRIMARY_QUANTITY
, LOT_NUMBER
, LOT_EXPIRATION_DATE
, ERROR_CODE
, SERIAL_TRANSACTION_TEMP_ID
, GROUP_HEADER_ID
, PUT_AWAY_RULE_ID
, PICK_RULE_ID
, DESCRIPTION
, VENDOR_NAME
, SUPPLIER_LOT_NUMBER
, ORIGINATION_DATE
, DATE_CODE
, GRADE_CODE
, CHANGE_DATE
, MATURITY_DATE
, STATUS_ID
, 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
, SUBLOT_NUM
, SECONDARY_QUANTITY
, SECONDARY_UNIT_OF_MEASURE
, QC_GRADE
, REASON_CODE
, PRODUCT_CODE
, PRODUCT_TRANSACTION_ID
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
)
VALUES
( l_transaction_temp_id
, l_mtlt_rec.LAST_UPDATE_DATE
, l_mtlt_rec.LAST_UPDATED_BY
, l_mtlt_rec.CREATION_DATE
, l_mtlt_rec.CREATED_BY
, l_mtlt_rec.LAST_UPDATE_LOGIN
, l_mtlt_rec.REQUEST_ID
, l_mtlt_rec.PROGRAM_APPLICATION_ID
, l_mtlt_rec.PROGRAM_ID
, l_mtlt_rec.PROGRAM_UPDATE_DATE
, 1
, 1
, l_mtlt_rec.LOT_NUMBER
, l_mtlt_rec.LOT_EXPIRATION_DATE
, l_mtlt_rec.ERROR_CODE
, l_mtlt_rec.SERIAL_TRANSACTION_TEMP_ID
, l_mtlt_rec.GROUP_HEADER_ID
, l_mtlt_rec.PUT_AWAY_RULE_ID
, l_mtlt_rec.PICK_RULE_ID
, l_mtlt_rec.DESCRIPTION
, l_mtlt_rec.VENDOR_NAME
, l_mtlt_rec.SUPPLIER_LOT_NUMBER
, l_mtlt_rec.ORIGINATION_DATE
, l_mtlt_rec.DATE_CODE
, l_mtlt_rec.GRADE_CODE
, l_mtlt_rec.CHANGE_DATE
, l_mtlt_rec.MATURITY_DATE
, l_mtlt_rec.STATUS_ID
, l_mtlt_rec.RETEST_DATE
, l_mtlt_rec.AGE
, l_mtlt_rec.ITEM_SIZE
, l_mtlt_rec.COLOR
, l_mtlt_rec.VOLUME
, l_mtlt_rec.VOLUME_UOM
, l_mtlt_rec.PLACE_OF_ORIGIN
, l_mtlt_rec.BEST_BY_DATE
, l_mtlt_rec.LENGTH
, l_mtlt_rec.LENGTH_UOM
, l_mtlt_rec.RECYCLED_CONTENT
, l_mtlt_rec.THICKNESS
, l_mtlt_rec.THICKNESS_UOM
, l_mtlt_rec.WIDTH
, l_mtlt_rec.WIDTH_UOM
, l_mtlt_rec.CURL_WRINKLE_FOLD
, l_mtlt_rec.LOT_ATTRIBUTE_CATEGORY
, l_mtlt_rec.C_ATTRIBUTE1
, l_mtlt_rec.C_ATTRIBUTE2
, l_mtlt_rec.C_ATTRIBUTE3
, l_mtlt_rec.C_ATTRIBUTE4
, l_mtlt_rec.C_ATTRIBUTE5
, l_mtlt_rec.C_ATTRIBUTE6
, l_mtlt_rec.C_ATTRIBUTE7
, l_mtlt_rec.C_ATTRIBUTE8
, l_mtlt_rec.C_ATTRIBUTE9
, l_mtlt_rec.C_ATTRIBUTE10
, l_mtlt_rec.C_ATTRIBUTE11
, l_mtlt_rec.C_ATTRIBUTE12
, l_mtlt_rec.C_ATTRIBUTE13
, l_mtlt_rec.C_ATTRIBUTE14
, l_mtlt_rec.C_ATTRIBUTE15
, l_mtlt_rec.C_ATTRIBUTE16
, l_mtlt_rec.C_ATTRIBUTE17
, l_mtlt_rec.C_ATTRIBUTE18
, l_mtlt_rec.C_ATTRIBUTE19
, l_mtlt_rec.C_ATTRIBUTE20
, l_mtlt_rec.D_ATTRIBUTE1
, l_mtlt_rec.D_ATTRIBUTE2
, l_mtlt_rec.D_ATTRIBUTE3
, l_mtlt_rec.D_ATTRIBUTE4
, l_mtlt_rec.D_ATTRIBUTE5
, l_mtlt_rec.D_ATTRIBUTE6
, l_mtlt_rec.D_ATTRIBUTE7
, l_mtlt_rec.D_ATTRIBUTE8
, l_mtlt_rec.D_ATTRIBUTE9
, l_mtlt_rec.D_ATTRIBUTE10
, l_mtlt_rec.N_ATTRIBUTE1
, l_mtlt_rec.N_ATTRIBUTE2
, l_mtlt_rec.N_ATTRIBUTE3
, l_mtlt_rec.N_ATTRIBUTE4
, l_mtlt_rec.N_ATTRIBUTE5
, l_mtlt_rec.N_ATTRIBUTE6
, l_mtlt_rec.N_ATTRIBUTE7
, l_mtlt_rec.N_ATTRIBUTE8
, l_mtlt_rec.N_ATTRIBUTE9
, l_mtlt_rec.N_ATTRIBUTE10
, l_mtlt_rec.VENDOR_ID
, l_mtlt_rec.TERRITORY_CODE
, l_mtlt_rec.SUBLOT_NUM
, l_mtlt_rec.SECONDARY_QUANTITY
, l_mtlt_rec.SECONDARY_UNIT_OF_MEASURE
, l_mtlt_rec.QC_GRADE
, l_mtlt_rec.REASON_CODE
, l_mtlt_rec.PRODUCT_CODE
, l_mtlt_rec.PRODUCT_TRANSACTION_ID
, l_mtlt_rec.ATTRIBUTE_CATEGORY
, l_mtlt_rec.ATTRIBUTE1
, l_mtlt_rec.ATTRIBUTE2
, l_mtlt_rec.ATTRIBUTE3
, l_mtlt_rec.ATTRIBUTE4
, l_mtlt_rec.ATTRIBUTE5
, l_mtlt_rec.ATTRIBUTE6
, l_mtlt_rec.ATTRIBUTE7
, l_mtlt_rec.ATTRIBUTE8
, l_mtlt_rec.ATTRIBUTE9
, l_mtlt_rec.ATTRIBUTE10
, l_mtlt_rec.ATTRIBUTE11
, l_mtlt_rec.ATTRIBUTE12
, l_mtlt_rec.ATTRIBUTE13
, l_mtlt_rec.ATTRIBUTE14
, l_mtlt_rec.ATTRIBUTE15
);
print_debug ('Inserted new MTLT', l_api_name);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = 1
, primary_quantity = 1
, reservation_quantity = DECODE( reservation_quantity
, NULL, NULL
, 1
)
, parent_line_id = msnt_rec.transaction_temp_id
, subinventory_code = parent_rec.subinventory_code
, locator_id = parent_rec.locator_id
, lpn_id = NVL( l_inner_lpn_id
, NVL( parent_rec.content_lpn_id
, parent_rec.lpn_id
)
)
WHERE transaction_temp_id = l_transaction_temp_id;
('Updated new temp ID ' || to_char(l_transaction_temp_id) ||
' with qty 1, sub ' || parent_rec.subinventory_code ||
', locator ID: ' || to_char(parent_rec.locator_id) ||
', LPN ID: ' || to_char( NVL( l_inner_lpn_id
, NVL( parent_rec.content_lpn_id
, parent_rec.lpn_id
)
)
) ||
', parent line ID: ' || to_char(msnt_rec.transaction_temp_id)
, l_api_name
);
UPDATE mtl_material_transactions_temp
SET subinventory_code = parent_rec.subinventory_code
, locator_id = parent_rec.locator_id
, lpn_id = NVL(parent_rec.content_lpn_id,parent_rec.lpn_id)
WHERE transaction_temp_id = l_transaction_temp_id;
('Updated temp ID ' || to_char(l_transaction_temp_id) ||
' with subinventory ' || parent_rec.subinventory_code ||
', locator ID: ' || to_char(parent_rec.locator_id) ||
' and LPN ID: ' || to_char(NVL( parent_rec.content_lpn_id
, parent_rec.lpn_id
)
)
, l_api_name
);
SELECT mtlt.serial_transaction_temp_id
INTO l_srl_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_transaction_temp_id
AND mtlt.lot_number = p_lot_number;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_srl_temp_id
FROM dual;
UPDATE mtl_transaction_lots_temp mtlt
SET mtlt.serial_transaction_temp_id = l_srl_temp_id
WHERE mtlt.transaction_temp_id = l_transaction_temp_id
AND mtlt.lot_number = p_lot_number;
UPDATE mtl_serial_numbers_temp msnt
SET transaction_temp_id = l_srl_temp_id
WHERE rowid = lot_msnt_rec.rowid;
UPDATE mtl_serial_numbers_temp msnt
SET transaction_temp_id = l_transaction_temp_id
WHERE rowid = msnt_rec.rowid;
( 'Updated MSNT record for serial ' || p_serial_number
, l_api_name
);
UPDATE mtl_material_transactions_temp
SET transaction_quantity = transaction_quantity - 1
, primary_quantity = primary_quantity - 1
WHERE transaction_temp_id = l_parent_line_id;
UPDATE mtl_transaction_lots_temp
SET transaction_quantity = transaction_quantity - 1
, primary_quantity = primary_quantity - 1
WHERE transaction_temp_id = l_parent_line_id
AND lot_number = p_lot_number;
SELECT mmtt.transaction_header_id
INTO l_txn_header_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
UPDATE mtl_serial_numbers
SET group_mark_id = l_txn_header_id
WHERE current_organization_id = p_organization_id
AND inventory_item_id = p_item_id
AND NVL(revision,'@@@@') = NVL(p_revision,'@@@@')
AND serial_number = p_serial_number;
( 'Updated MSN record with header_id ' || to_char(l_txn_header_id)
, l_api_name
);
g_cur_pend_temp.DELETE(ii);
SELECT 'x'
INTO l_dummy
FROM dual
WHERE EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = g_current_drop_lpn.organization_id
AND mmtt.transfer_lpn_id = g_current_drop_lpn.lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.transaction_quantity > 0
AND mmtt.inventory_item_id = p_item_id
AND NVL(mmtt.revision,'@@@@') = NVL(p_revision,'@@@@')
AND ( mmtt.content_lpn_id IS NULL
OR
mmtt.content_lpn_id = mmtt.transfer_lpn_id
)
);
SELECT mmtt.transaction_temp_id
, wdt.suggested_dest_subinventory
, wdt.suggested_dest_locator_id
FROM ( SELECT mmtt2.transaction_temp_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.parent_line_id IS NULL
UNION
SELECT mmtt3.transaction_temp_id
FROM mtl_material_transactions_temp mmtt3
WHERE mmtt3.parent_line_id IS NOT NULL
AND mmtt3.transaction_temp_id <> mmtt3.parent_line_id
AND mmtt3.transaction_temp_id IN
( SELECT mmtt4.transaction_temp_id
FROM mtl_material_transactions_temp mmtt4
WHERE mmtt4.organization_id = p_org_id
AND mmtt4.transfer_lpn_id = p_lpn_id
START WITH
mmtt4.transaction_temp_id = mmtt4.parent_line_id
CONNECT BY
( mmtt4.parent_line_id = PRIOR mmtt4.transaction_temp_id
AND
mmtt4.parent_line_id <> mmtt4.transaction_temp_id
)
)
) mmtt
, wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
AND wdt.suggested_dest_subinventory IS NOT NULL
AND wdt.suggested_dest_locator_id IS NOT NULL;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = v_sub_tbl(ii)
, transfer_to_location = v_loc_id_tbl(ii)
WHERE transaction_temp_id = v_temp_id_tbl(ii);
print_debug('Updated dest sub, dest loc on MMTTs', l_api_name);
UPDATE wms_dispatched_tasks
SET suggested_dest_subinventory = NULL
, suggested_dest_locator_id = NULL
WHERE transaction_temp_id = v_temp_id_tbl(jj);
print_debug('Updated suggested dest sub/loc on WDTs to NULL', l_api_name);
DELETE wms_dispatched_tasks
WHERE transaction_temp_id IN
( SELECT transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id IS NOT NULL
AND mmtt.transaction_temp_id <> NVL(mmtt.parent_line_id,0)
AND mmtt.transaction_temp_id IN
( SELECT mmtt2.transaction_temp_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_organization_id
AND mmtt2.transfer_lpn_id = p_transfer_lpn_id
START WITH
mmtt2.transaction_temp_id = mmtt2.parent_line_id
CONNECT BY
( mmtt2.parent_line_id = PRIOR mmtt2.transaction_temp_id
AND
mmtt2.parent_line_id <> mmtt2.transaction_temp_id
)
)
);
print_debug ('No. of child WDT records deleted: ' || SQL%ROWCOUNT, l_api_name);
UPDATE wms_license_plate_numbers wlpn1
SET wlpn1.lpn_context = WMS_Container_PUB.LPN_CONTEXT_PACKING
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE wlpn1.lpn_id IN
( SELECT mmtt.content_lpn_id
FROM mtl_material_transactions_temp mmtt
, wms_license_plate_numbers wlpn2
WHERE mmtt.transfer_lpn_id = p_transfer_lpn_id
AND mmtt.organization_id = p_organization_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id = wlpn2.lpn_id
AND mmtt.organization_id = wlpn2.organization_id
AND wlpn2.lpn_context = WMS_Container_PUB.LPN_CONTEXT_INV
);
SELECT lpn_id
, lpn_context
, subinventory_code
, locator_id
FROM wms_license_plate_numbers
WHERE organization_id = p_org_id
AND license_plate_number = p_lpn;
SELECT wda.delivery_id
FROM wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
WHERE wdd.lpn_id = p_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = p_org_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id;
SELECT mmtt.transfer_subinventory
, mmtt.transfer_to_location
, mtrl.carton_grouping_id
, wda.delivery_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrl.line_id = wdd.move_order_line_id (+)
AND wdd.delivery_detail_id = wda.delivery_detail_id (+);
SELECT mtrl.carton_grouping_id
FROM wsh_delivery_details_ob_grp_v wdd
, wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd2
, mtl_txn_request_lines mtrl
WHERE wdd.lpn_id = p_lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd.organization_id = p_org_id
AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
AND wda.delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.move_order_line_id = mtrl.line_id;
SELECT mtrl.carton_grouping_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
WHERE mmtt.transaction_temp_id = p_temp_id
AND mmtt.move_order_line_id = mtrl.line_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING primary_quantity
INTO l_primary_qty;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
( 'Deleted temp ID: ' || to_char(l_transaction_temp_id)
, l_api_name
);
l_temp_tbl.DELETE;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING primary_quantity
INTO l_primary_qty;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
( 'Deleted temp ID: ' || to_char(l_transaction_temp_id)
, l_api_name
);
l_temp_tbl.DELETE;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING primary_quantity
INTO l_primary_qty;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
( 'Deleted temp ID: ' || to_char(l_transaction_temp_id)
, l_api_name
);
l_temp_tbl.DELETE;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_lpn_id = NVL(l_lpn_id,to_lpn_rec.lpn_id)
WHERE mmtt.transaction_temp_id = l_transaction_temp_id
RETURNING primary_quantity
INTO l_primary_qty;
DELETE mtl_material_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id;
g_current_drop_lpn.current_drop_list.DELETE(l_transaction_temp_id);
( 'Deleted temp ID: ' || to_char(l_transaction_temp_id)
, l_api_name
);
l_temp_tbl.DELETE;
PROCEDURE insert_mmtt_pack
( x_pack_temp_id OUT NOCOPY NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, p_parent_temp_id IN NUMBER
, p_lpn_id IN NUMBER
, p_outer_lpn_id IN NUMBER
) IS
l_api_name VARCHAR2(30) := 'insert_mmtt_pack';
SAVEPOINT insert_pack_sp;
SELECT mmtt.transaction_temp_id
INTO l_child_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_parent_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND ROWNUM < 2;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_pack_temp_id
FROM DUAL;
( 'About to insert pack txn with temp ID: '
|| to_char(l_pack_temp_id)
, l_api_name
);
INSERT INTO mtl_material_transactions_temp
( transaction_header_id
, transaction_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, subinventory_code
, locator_id
, transaction_quantity
, primary_quantity
, transaction_uom
, transaction_cost
, transaction_type_id
, transaction_action_id
, transaction_source_type_id
, transaction_source_id
, transaction_source_name
, transaction_date
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, move_order_line_id
, task_group_id
, pick_slip_number
, reservation_id
, transaction_status
, transfer_cost_group_id
, lpn_id
, transfer_lpn_id
, content_lpn_id
, operation_plan_id
, transaction_batch_id
, transaction_batch_seq
)
(SELECT transaction_header_id
, l_pack_temp_id
, source_code
, source_line_id
, transaction_mode
, lock_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, inventory_item_id
, revision
, organization_id
, transfer_subinventory
, transfer_to_location
, 1
, 1
, transaction_uom
, transaction_cost
, INV_GLOBALS.G_TYPE_CONTAINER_PACK
, INV_GLOBALS.G_ACTION_CONTAINERPACK
, INV_GLOBALS.G_SOURCETYPE_INVENTORY
, NULL
, NULL
, SYSDATE
, acct_period_id
, distribution_account_id
, transaction_reference
, requisition_line_id
, requisition_distribution_id
, reason_id
, lot_number
, lot_expiration_date
, serial_number
, receiving_document
, demand_id
, rcv_transaction_id
, move_transaction_id
, completion_transaction_id
, wip_entity_type
, schedule_id
, repetitive_line_id
, employee_code
, primary_switch
, schedule_update_code
, setup_teardown_code
, item_ordering
, negative_req_flag
, operation_seq_num
, picking_line_id
, trx_source_line_id
, trx_source_delivery_id
, physical_adjustment_id
, cycle_count_id
, rma_line_id
, customer_ship_id
, currency_code
, currency_conversion_rate
, currency_conversion_type
, currency_conversion_date
, ussgl_transaction_code
, vendor_lot_number
, encumbrance_account
, encumbrance_amount
, ship_to_location
, shipment_number
, transfer_cost
, transportation_cost
, transportation_account
, freight_code
, containers
, waybill_airbill
, expected_arrival_date
, transfer_subinventory
, transfer_organization
, transfer_to_location
, new_average_cost
, value_change
, percentage_change
, material_allocation_temp_id
, demand_source_header_id
, demand_source_line
, demand_source_delivery
, item_segments
, item_description
, item_trx_enabled_flag
, item_location_control_code
, item_restrict_subinv_code
, item_restrict_locators_code
, item_revision_qty_control_code
, item_primary_uom_code
, item_uom_class
, item_shelf_life_code
, item_shelf_life_days
, item_lot_control_code
, item_serial_control_code
, item_inventory_asset_flag
, allowed_units_lookup_code
, department_id
, department_code
, wip_supply_type
, supply_subinventory
, supply_locator_id
, valid_subinventory_flag
, valid_locator_flag
, locator_segments
, current_locator_control_code
, number_of_lots_entered
, wip_commit_flag
, next_lot_number
, lot_alpha_prefix
, next_serial_number
, serial_alpha_prefix
, shippable_flag
, posting_flag
, required_flag
, process_flag
, ERROR_CODE
, error_explanation
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, movement_id
, reservation_quantity
, shipped_quantity
, transaction_line_number
, task_id
, to_task_id
, source_task_id
, project_id
, source_project_id
, pa_expenditure_org_id
, to_project_id
, expenditure_type
, final_completion_flag
, transfer_percentage
, transaction_sequence_id
, material_account
, material_overhead_account
, resource_account
, outside_processing_account
, overhead_account
, flow_schedule
, cost_group_id
, demand_class
, qa_collection_id
, kanban_card_id
, overcompletion_transaction_id
, overcompletion_primary_qty
, overcompletion_transaction_qty
, end_item_unit_number
, scheduled_payback_date
, line_type_code
, parent_transaction_temp_id
, put_away_strategy_id
, put_away_rule_id
, pick_strategy_id
, pick_rule_id
, common_bom_seq_id
, common_routing_seq_id
, cost_type_id
, org_cost_group_id
, NULL
, task_group_id
, pick_slip_number
, NULL
, 3
, transfer_cost_group_id
, NULL
, p_outer_lpn_id
, p_lpn_id
, operation_plan_id
, transaction_header_id
, l_pack_temp_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_child_temp_id
);
ROLLBACK to insert_pack_sp;
ROLLBACK to insert_pack_sp;
END insert_mmtt_pack;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NOT NULL
AND EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.content_lpn_id = mmtt.lpn_id
AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
AND mmtt2.parent_line_id <> mmtt.parent_line_id
)
UNION ALL
-- Nested LPNs
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NOT NULL
AND EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.content_lpn_id = mmtt.lpn_id
AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
AND mmtt2.parent_line_id = mmtt.parent_line_id
)
UNION ALL
-- Picked from LPN that is not a nested LPN
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NOT NULL
AND NOT EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.content_lpn_id = mmtt.lpn_id
AND mmtt2.transaction_temp_id = mmtt2.parent_line_id
)
UNION ALL
-- Loose material
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NULL;
SELECT mmtt.transaction_temp_id
, mmtt.content_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND mmtt.content_lpn_id IS NOT NULL;
SELECT 'x'
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.parent_line_id = p_parent_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id
AND mmtt.transaction_action_id in (2,28)
AND EXISTS
( SELECT 'x'
FROM mtl_secondary_inventories msi
WHERE msi.secondary_inventory_name = mmtt.transfer_subinventory
AND msi.organization_id = mmtt.organization_id
AND NVL(msi.disable_date,sysdate + 1)
> sysdate
AND msi.lpn_controlled_flag = 1
);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_lpn_id = nested_lpn_rec.content_lpn_id
, mmtt.lpn_id = NVL(mmtt.lpn_id,nested_lpn_rec.content_lpn_id) -- Bug 5620764
WHERE mmtt.parent_line_id = nested_lpn_rec.transaction_temp_id
AND mmtt.parent_line_id <> mmtt.transaction_temp_id;
('Updated transfer LPN on child records for parent '
|| to_char(nested_lpn_rec.transaction_temp_id)
, l_api_name
);
insert_mmtt_pack
( x_pack_temp_id => l_pack_temp_id
, x_return_status => l_api_return_status
, p_parent_temp_id => nested_lpn_rec.transaction_temp_id
, p_lpn_id => nested_lpn_rec.content_lpn_id
, p_outer_lpn_id => p_xfer_lpn_id
);
print_debug ('insert_mmtt_pack returned an error', l_api_name);
SELECT wlpn.lpn_id
INTO l_outer_most_lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = p_drop_lpn;
insert_mmtt_pack
( x_pack_temp_id => l_pack_temp_id
, x_return_status => l_api_return_status
, p_parent_temp_id => l_parent_temp_id
, p_lpn_id => p_xfer_lpn_id
, p_outer_lpn_id => l_outer_most_lpn_id
);
print_debug ('insert_mmtt_pack returned an error', l_api_name);
SELECT mmtt.transaction_temp_id --bug#6323330. Added org,item,revision,sub,loc in SELECT clause.
FROM (
-- Material unpacked from nested LPNs
SELECT mmtt.transaction_temp_id,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NULL
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NOT NULL
AND EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.content_lpn_id = mmtt.lpn_id
AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
)
UNION ALL
-- Nested LPNs
SELECT mmtt.transaction_temp_id ,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NULL
AND mmtt.content_lpn_id IS NOT NULL
UNION ALL
-- Picked from LPN that is not a nested LPN
SELECT mmtt.transaction_temp_id,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NULL
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NOT NULL
AND NOT EXISTS
( SELECT 'x'
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = p_org_id
AND mmtt2.transfer_lpn_id = p_lpn_id
AND mmtt2.content_lpn_id = mmtt.lpn_id
AND mmtt2.transaction_temp_id <> mmtt.transaction_temp_id
)
UNION ALL
--Added for Bug 6717052
SELECT mmtt.transaction_temp_id ,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id IN (SELECT lpn_id FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_lpn_id AND lpn_id <> outermost_lpn_id)
AND mmtt.parent_line_id IS NULL
UNION ALL
-- Loose material
SELECT mmtt.transaction_temp_id,
mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NULL
AND mmtt.content_lpn_id IS NULL
AND mmtt.lpn_id IS NULL
ORDER BY 2,3,4,5,6,7 ) mmtt, wms_dispatched_tasks wdt
WHERE mmtt.TRANSACTION_TEMP_ID=wdt.TRANSACTION_TEMP_ID(+)
ORDER BY mmtt.organization_id,
mmtt.inventory_item_id,
mmtt.revision ,
mmtt.subinventory_code,
mmtt.locator_id,
mmtt.transaction_date,
wdt.loaded_time; --Bug#6267350 -- Modified for bug#9247514
SELECT mmtt.parent_line_id
, mmtt.transaction_type_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT mmtt.transaction_temp_id
, wdt.task_id
FROM mtl_material_transactions_temp mmtt
, wms_dispatched_tasks wdt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND mmtt.parent_line_id IS NOT NULL
AND mmtt.parent_line_id = mmtt.transaction_temp_id
AND wdt.transaction_temp_id = mmtt.transaction_temp_id;
SELECT mmtt.serial_allocated_flag
FROM mtl_material_transactions_temp mmtt
WHERE transaction_temp_id = p_temp_id;
SELECT wlpn.lpn_id
INTO l_drop_lpn_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = p_drop_lpn
AND wlpn.organization_id = p_organization_id
AND ROWNUM=1;
SELECT mil.inventory_location_type
INTO l_to_loc_type
FROM mtl_item_locations mil
WHERE mil.organization_id = p_organization_id
AND mil.subinventory_code = p_subinventory
AND mil.inventory_location_id = p_loc_id;
IF (l_debug = 1) THEN print_debug('exception selecting to_loc_type', l_api_name ); END IF;
SELECT mmtt.transfer_lpn_id,
mmtt.inventory_item_id,
mmtt.transaction_uom,
mmtt.transaction_quantity
INTO l_curr_xfer_lpn_id, l_inventory_item_id, l_transaction_uom, l_transaction_qty
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
UPDATE wms_license_plate_numbers wlpn
SET wlpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_PACKING
, last_update_date = SYSDATE
, last_updated_by = fnd_global.user_id
WHERE wlpn.lpn_id = l_curr_xfer_lpn_id
AND wlpn.lpn_context = WMS_Container_PUB.LPN_CONTEXT_PREGENERATED;
('Updated LPN context to packing for LPN ID ' || to_char(l_curr_xfer_lpn_id)
, l_api_name
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM dual;
insert_child_msnt
( x_return_status => l_api_return_status
, p_temp_id => l_transaction_temp_id
, p_parent_tmp_id => l_parent_line_id
, p_txn_header_id => l_txn_header_id
);
print_debug ('Error from insert_child_msnt', l_api_name);
print_debug ('Success status from insert_child_msnt', l_api_name);
SELECT wdt.task_id
INTO l_task_id
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = l_transaction_temp_id;
INSERT INTO wms_dispatched_tasks
( task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, equipment_id
, equipment_instance
, person_resource_id
, machine_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, loaded_time
, operation_plan_id
, move_order_line_id
, suggested_dest_subinventory --bug 7319244
, suggested_dest_locator_id --bug 7319244
)
( SELECT wms_dispatched_tasks_s.NEXTVAL
, l_transaction_temp_id
, mmtt.organization_id
, wdt.user_task_type
, wdt.person_id
, wdt.effective_start_date
, wdt.effective_end_date
, wdt.equipment_id
, wdt.equipment_instance
, wdt.person_resource_id
, wdt.machine_resource_id
, 4
, wdt.dispatched_time
, SYSDATE
, p_emp_id
, SYSDATE
, p_emp_id
, wdt.task_type
, SYSDATE
, mmtt.operation_plan_id
, mmtt.move_order_line_id
, mmtt.transfer_subinventory --bug 7319244
, mmtt.transfer_to_location --bug 7319244
FROM wms_dispatched_tasks wdt
, mtl_material_transactions_temp mmtt
WHERE wdt.transaction_temp_id = l_parent_line_id
AND mmtt.transaction_temp_id = l_transaction_temp_id
AND rownum = 1
);
SELECT wdt.task_id
INTO l_task_id
FROM wms_dispatched_tasks wdt
WHERE wdt.transaction_temp_id = l_transaction_temp_id;
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.parent_line_id = NULL
WHERE transaction_temp_id = l_transaction_temp_id;
print_debug( 'Updated MMTT', l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_header_id = l_txn_header_id
, mmtt.subinventory_code = p_subinventory
, mmtt.locator_id = p_loc_id
, mmtt.transfer_subinventory = p_subinventory
, mmtt.transfer_to_location = p_loc_id
, mmtt.transaction_batch_id = l_txn_header_id
, mmtt.transaction_batch_seq = l_batch_seq_id
WHERE transaction_temp_id = l_transaction_temp_id;
( 'Updated packing txn ' || to_char(l_transaction_temp_id) ||
' - set batch/header ID to ' || to_char(l_txn_header_id) ||
', batch sequence to ' || to_char(l_batch_seq_id) ||
', sub and xfer sub to ' || p_subinventory ||
', loc and xfer loc to ' || to_char(p_loc_id)
, l_api_name
);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transfer_subinventory = p_subinventory,
mmtt.transfer_to_location = p_loc_id,
mmtt.transaction_batch_id = l_txn_header_id,
mmtt.transaction_batch_seq = l_batch_seq_id
WHERE transaction_temp_id = l_transaction_temp_id;
( 'Updated subtransfer txn ' || to_char(l_transaction_temp_id) ||
' - set batch/header ID to ' || to_char(l_txn_header_id) ||
', batch sequence to ' || to_char(l_batch_seq_id) ||
', xfer sub to ' || p_subinventory ||
', xfer loc to ' || to_char(p_loc_id)
, l_api_name);
UPDATE mtl_material_transactions_temp mmtt
SET mmtt.transaction_batch_seq = l_batch_seq_id
WHERE transaction_temp_id = l_transaction_temp_id;
( 'Updated temp ID ' || to_char(l_transaction_temp_id) ||
' - set batch sequence to ' || to_char(l_batch_seq_id)
, l_api_name
);
l_temp_tbl.DELETE(ii);
l_temp_tbl.DELETE(kk);
SELECT mmtt.transfer_lpn_id
INTO l_next_xfer_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = l_transaction_temp_id;
UPDATE wms_dispatched_tasks_history wdth
SET wdth.parent_transaction_id = l_parent_line_id
, wdth.is_parent = 'N'
WHERE task_id = l_task_id;
( 'Updated WDTH: set parent_transaction_id = '
|| to_char(l_parent_line_id)
, l_api_name
);
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_txn_header_id
FROM dual;
, p_delete_mmtt_flag => 'Y'
, p_txn_header_id => l_txn_header_id
, p_transfer_lpn_id => p_transfer_lpn_id
);
UPDATE wms_dispatched_tasks_history wdth
SET wdth.parent_transaction_id = wdth.transaction_id
, wdth.is_parent = 'Y'
WHERE wdth.task_id = parent_rec.task_id
RETURNING wdth.transaction_id INTO l_parent_txn_id;
( 'Updated WDTH. Parent transaction ID is: '
|| to_char(l_parent_txn_id)
, l_api_name
);
UPDATE wms_dispatched_tasks_history wdth
SET wdth.parent_transaction_id = l_parent_txn_id
WHERE wdth.parent_transaction_id = parent_rec.transaction_temp_id;
( 'Updated WDTH for child records.'
, l_api_name
);
l_task_tbl.DELETE;
DELETE mtl_allocations_gtmp;
g_current_drop_lpn.current_drop_list.DELETE;
g_current_drop_lpn.temp_id_group_ref.DELETE;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = p_org_id
AND mmtt.transfer_lpn_id = p_lpn_id
AND ( (mmtt.parent_line_id IS NOT NULL
AND
mmtt.parent_line_id <> mmtt.transaction_temp_id
)
OR
mmtt.parent_line_id IS NULL
);
l_temp_tbl.DELETE;
SELECT name
FROM wsh_new_deliveries_ob_grp_v wnd
WHERE wnd.delivery_id = p_del_id;
SELECT MIN(ooha.order_number)
, COUNT(DISTINCT ooha.order_number)
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, oe_order_lines_all oola
, oe_order_headers_all ooha
WHERE wda.delivery_id = p_del_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.source_line_id = oola.line_id
AND oola.header_id = ooha.header_id;
SELECT mcr.inventory_item_id
, mcr.uom_code
, mir.revision
FROM mtl_cross_references mcr
, mtl_item_revisions mir
WHERE mcr.organization_id = mir.organization_id (+)
AND mcr.inventory_item_id = mir.inventory_item_id (+)
AND mcr.revision_id = mir.revision_id (+)
AND mcr.cross_reference_type = p_ref_type
AND mcr.cross_reference = p_xref
AND (mcr.organization_id = p_org_id
OR
mcr.org_independent_flag = 'Y'
);
SELECT concatenated_segments
FROM mtl_system_items_kfv msik
WHERE msik.organization_id = p_org_id
AND msik.inventory_item_id = p_item_id;
SELECT wlpn.lpn_id
, wlpn.lpn_context
, wlpn.subinventory_code
, wlpn.locator_id
FROM wms_license_plate_numbers wlpn
WHERE wlpn.license_plate_number = p_lpn
AND wlpn.organization_id = p_org_id;
SELECT mmtt.transfer_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT wda.delivery_id
FROM wsh_delivery_assignments_v wda
, wsh_delivery_details_ob_grp_v wdd
, wms_license_plate_numbers lpn
WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
AND wdd.organization_id = p_org_id
AND wdd.lpn_id = lpn.lpn_id
AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
AND lpn.outermost_lpn_id = p_lpn_id
ORDER BY wda.delivery_id;
l_temp_tbl.DELETE;
l_temp_tbl.DELETE;
SELECT delivery_detail_id
INTO l_line_rows(1)
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id = drop_lpn_rec.lpn_id
AND rownum = 1 ;
SELECT wdd.delivery_detail_id
INTO l_line_rows(2)
FROM wsh_delivery_details_ob_grp_v wdd, Mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_line_id = wdd.move_order_line_id
AND wdd.organization_id = mmtt.organization_id
AND mmtt.organization_id= p_organization_id
AND mmtt.transfer_lpn_id= p_transfer_lpn_id
AND rownum = 1 ;