[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lot_number,lot_qty,serial_number
FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_txn_temp_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_summary = 'N';
SELECT COUNT(*),SUM(lot_qty) INTO l_count_child_rec,l_total_lot_qty
FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_txn_temp_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_summary = 'N';
SELECT 1 INTO l_lot_cnt FROM dual WHERE exists
(SELECT lot_number FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id
AND lot_number = l_child_rec.lot_number );
SELECT TRANSACTION_QUANTITY INTO l_orig_lot_qty
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_txn_temp_id
AND lot_number = l_child_rec.lot_number;
SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
(SELECT fm_serial_number--What if RANGE serials ??
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_txn_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number = l_child_rec.serial_number);
SELECT 1 INTO l_serial_cnt FROM dual WHERE exists
(SELECT fm_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_txn_temp_id
AND msnt.fm_serial_number = l_child_rec.serial_number);
l_LAST_UPDATE_DATE DATE;
l_last_updated_by NUMBER ;
SELECT relation_id -- all following are passed by WCS
, task_id
, task_summary
, business_event_id
, transaction_quantity
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, organization_id--Its NOT NULL Column
, status_code
, status_msg
, lot_number
, lot_qty
, serial_number
, device_id
FROM wms_device_requests
WHERE business_event_id IN (wms_device_integration_pvt.wms_be_load_confirm,wms_device_integration_pvt.wms_be_task_confirm)
ORDER BY relation_id ASC,task_id ASC, task_summary desc;
SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
SELECT
DEVICE_ID
,responsibility_application_id
,responsibility_id
INTO
l_device_id
,l_resp_application_id
,l_resp_id
FROM wms_device_requests_hist
WHERE request_id = nvl(l_rec.relation_id,-1)
--nvl,to handle ERROR: when WCS does NOT pass relation_id or task_id
AND task_id = nvl(l_rec.task_id,-1)
AND task_summary = 'Y'
AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND ROWNUM<2;
INSERT INTO wms_device_requests_hist(request_id
, relation_id -- parent_request_id
, task_id
, business_event_id
, transaction_quantity
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, task_summary
, organization_id
, device_id
, request_date
, requested_by
, status_code
, status_msg
, responsibility_application_id
, responsibility_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, lot_number
, lot_qty
, serial_number
) VALUES (l_request_id
, l_rec.relation_id
, l_rec.task_id
, l_rec.business_event_id
, l_rec.transaction_quantity
, l_rec.transfer_sub_code
, l_rec.transfer_loc_id
, l_rec.lpn_id
, l_rec.xfer_lpn_id
, l_rec.device_status
, l_rec.reason_id
, l_rec.task_summary
, l_rec.organization_id
, l_device_id
, SYSDATE
, fnd_global.USER_ID
, nvl(l_rec.status_code,'E') --Bug#4535546.Added nvl
, l_rec.status_msg
, l_resp_application_id
, l_resp_id
, SYSDATE
, fnd_global.USER_ID
, SYSDATE
, fnd_global.USER_ID
, l_rec.lot_number
, l_rec.lot_qty
, l_rec.serial_number
);
PROCEDURE update_wdr_for_error_rec(p_task_id IN NUMBER
,p_relation_id IN NUMBER) IS
l_status_msg VARCHAR2(240);
UPDATE wms_device_requests
SET status_code = 'E',
status_msg = l_status_msg
WHERE business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_id = p_task_id
AND relation_id = p_relation_id;
END update_wdr_for_error_rec;
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND Nvl(lot_number,-999) = Nvl(p_lot,-999);
SELECT
mtlt.primary_quantity,
mtlt.lot_number
FROM
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id;
t_lpn_lot_qty_table.delete;
SELECT 1,
lpn_context
INTO l_lpn_exists,
l_lpn_context
FROM wms_license_plate_numbers wlpn
WHERE wlpn.organization_id = p_org_id
AND wlpn.lpn_id = p_lpn;
SELECT 1
INTO l_loaded
FROM dual
WHERE exists
( SELECT 1
from mtl_material_transactions_temp
where transaction_header_id
=(SELECT transaction_header_id
from mtl_material_transactions_temp
WHERE transaction_temp_id=p_temp_id)
AND (transfer_lpn_id=p_lpn OR content_lpn_id=p_lpn)
AND cost_group_id = p_cost_group_id);
SELECT
w.subinventory_code,
INV_PROJECT.GET_LOCSEGS(w.locator_id, w.organization_id),
w.license_plate_number,
w.locator_id,
w.lpn_context
INTO
l_sub,
l_loc,
l_from_lpn,
l_loc_id,
l_lpn_context
FROM
wms_license_plate_numbers w
WHERE
w.lpn_id = p_lpn
AND w.locator_id is not null;
SELECT COUNT(*)
INTO l_sub_active
FROM mtl_secondary_inventories
WHERE Nvl(disable_date, Sysdate+1) > Sysdate
AND organization_id = p_org_id
AND secondary_inventory_name = l_sub;
SELECT COUNT(*)
INTO l_loc_active
FROM mtl_item_locations_kfv
WHERE Nvl(disable_date, sysdate+1) > sysdate
AND organization_id = p_org_id
AND subinventory_code = l_sub
AND inventory_location_id = l_loc_id;
SELECT 1
INTO l_so_cnt
FROM dual
WHERE exists
(SELECT 1
FROM wsh_delivery_details
WHERE lpn_id=p_lpn
AND organization_id=p_org_id
);
SELECT
primary_uom_code,
lot_control_code,
serial_number_control_code
INTO
l_primary_uom,
l_lot_code,
l_serial_code
FROM mtl_system_items
WHERE organization_id = p_org_id
AND inventory_item_id = p_item_id;
SELECT mmtt.transfer_subinventory
INTO l_xfr_sub_code
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id = p_temp_id;
SELECT 1 INTO l_item_cnt FROM DUAL WHERE exists
( SELECT 1
FROM wms_lpn_contents wlc
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND Nvl(wlc.revision,'-999') = Nvl(p_rev,Nvl(wlc.revision,'-999'))); --bug 2495592
SELECT 1 INTO l_item_cnt FROM DUAL WHERE exists
( SELECT 1
FROM wms_lpn_contents wlc,
mtl_transaction_lots_temp mtlt
WHERE wlc.parent_lpn_id = p_lpn
AND wlc.organization_id = p_org_id
AND wlc.inventory_item_id = p_item_id
AND Nvl(wlc.revision,'-999') = Nvl(p_rev,Nvl(wlc.revision,'-999'))
AND (mtlt.transaction_temp_id = p_temp_id
AND mtlt.lot_number = wlc.lot_number));
select allocated_lpn_id
into l_allocated_lpn_id
from mtl_material_transactions_temp
where transaction_temp_id = p_temp_id;
SELECT count( distinct inventory_item_id ),
count( distinct lot_number ),
count( distinct revision ) ,
count( distinct cost_group_id )
INTO l_item_cnt2,
l_lot_cnt,
l_rev_cnt,
l_cg_cnt
FROM wms_lpn_contents
WHERE parent_lpn_id = p_lpn
AND organization_id = p_org_id;
select count(*)
into l_lpn_include_lpn
from wms_license_plate_numbers
where outermost_lpn_id = p_lpn
and organization_id = p_org_id;
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update qty tree for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update qty tree without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back without lpn 1st time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update qty tree back for lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back with lpn 1st time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update qty tree back without lpn l_att:' || l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back without lpn 1st time failed ');
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN
( SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
);
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree with lpn 2nd time: l_att:' || l_att);
trace('lpn_match: calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree without lpn 2nd time:l_att:'||l_att);
trace('lpn_match: calling update qty tree back without lpn 2nd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree back with lpn 2nd time: l_att:' || l_att);
trace('lpn_match: calling update qty tree with lpn 2nd time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => p_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree back without lpn 2nd time:l_att:'||l_att);
trace('lpn_match: calling update qty tree back without lpn 2nd time failed ');
SELECT
primary_quantity,
transaction_uom
INTO
l_mmtt_qty,
l_txn_uom
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = p_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
AND msnt.fm_serial_number IN
( SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
);
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back 3rd time without lpn 3rd time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree back 3rd time for lpn l_att:'||l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree with lpn 3rd time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => TRUE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => l_mtlt_lot_number
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mtlt_primary_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: after update qty tree back without lpn 3rd time l_att:'|| l_att||' for lot:'||l_mtlt_lot_number);
trace('lpn_match: calling update qty tree back without lpn 3rd time failed ');
SELECT COUNT(fm_serial_number)
INTO l_total_serial_cnt
FROM mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = p_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT COUNT(fm_serial_number)
INTO l_serial_exist_cnt
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = p_temp_id
AND msnt.fm_serial_number IN
( SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = p_lpn
AND inventory_item_id = p_item_id
AND Nvl(revision, '-999') = Nvl(p_rev, '-999')
);
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree with lpn 4th time: l_att:' || l_att);
trace('lpn_match: calling update qty tree with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => -l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree without lpn 4th time:l_att:'||l_att);
trace('lpn_match: calling update qty tree without lpn 4th time failed ');
--from lpn is the same as allocated_lpn, we need to update qty tree as negative qty
-- in order to get correct att.
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, p_lpn_id => p_lpn
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree back with lpn 4th time: l_att:' || l_att);
trace('lpn_match: calling update qty tree back with lpn 4th time failed ');
inv_quantity_tree_pub.update_quantities
( p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_return_status
, x_msg_count => l_msg_cnt
, x_msg_data => l_msg_data
, p_organization_id => p_org_id
, p_inventory_item_id => p_item_id
, p_tree_mode => INV_Quantity_Tree_PUB.g_transaction_mode
, p_is_revision_control => b_is_revision_control
, p_is_lot_control => FALSE
, p_is_serial_control => b_is_serial_control
, p_revision => nvl(p_rev, NULL)
, p_lot_number => null
, p_subinventory_code => l_sub
, p_locator_id => l_loc_id
, p_primary_quantity => l_mmtt_qty
, p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
-- , p_lpn_id => p_lpn withour lpn_id, only to locator level
, p_transfer_subinventory_code => l_xfr_sub_code
);
trace('lpn_match: update qty tree back without lpn 4th time:l_att:'||l_att);
trace('lpn_match: calling update qty tree back without lpn 4th time failed ');
SELECT
COUNT(fm_serial_number)
INTO
l_total_serial_cnt
FROM
mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id=p_temp_id;
l_last_updated_by NUMBER;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id;
SELECT
msnt.fm_serial_number,
msnt.to_serial_number
FROM
mtl_serial_numbers_temp msnt,
mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id
AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
SELECT relation_id -- parent_request_id
, task_id
, task_summary
, business_event_id
, transaction_quantity
, transfer_sub_code
, transfer_loc_id
, lpn_id
, xfer_lpn_id
, device_status
, reason_id
, organization_id--Its NOT NULL Column
FROM wms_device_requests
WHERE business_event_id IN
(wms_device_integration_pvt.wms_be_task_confirm, wms_device_integration_pvt.wms_be_load_confirm)
AND device_status = 'S'
AND task_summary = 'Y'
ORDER BY xfer_lpn_id;
CURSOR c_mtlt_update (p_relation_id NUMBER, p_temp_id NUMBER) IS
SELECT lot_number,lot_qty FROM wms_device_requests
WHERE relation_id = p_relation_id
AND task_id = p_temp_id
AND task_summary = 'N'
AND business_event_id IN
(wms_device_integration_pvt.wms_be_task_confirm,
wms_device_integration_pvt.wms_be_load_confirm);
CURSOR c_update_xfer_lpns_context IS
SELECT wlpn.lpn_id,organization_id FROM wms_license_plate_numbers wlpn
WHERE wlpn.lpn_context <> wms_container_pub.lpn_context_pregenerated
--to avoid LPNS that have been unpacked by TM for non-LPN ctrld sub
AND wlpn.lpn_id IN
(SELECT wdr.xfer_lpn_id
FROM wms_device_requests wdr,
wms_device_requests_hist wdrh,
wms_dispatched_tasks wdt
WHERE wdr.business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND wdr.task_id = wdt.transaction_temp_id
AND wdt.task_type IN (4,5,7) -- ONLY for Replenishment, MO Xfer,Staging TASKS
AND wdr.status_code = 'S'
AND wdr.device_status = 'S'
AND wdr.task_summary = 'Y'
AND wdr.task_summary = wdrh.task_summary
and wdrh.request_id = wdr.relation_id
AND wdr.task_id = wdrh.task_id
AND wdr.transaction_quantity > 0
AND wdrh.business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND wdrh.TASK_TYPE_ID = 1);
SELECT transaction_temp_id, organization_id, transfer_lpn_id, content_lpn_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_header_id = l_txn_hdr_id;
CURSOR c_open_period_check IS SELECT distinct organization_id, task_id
FROM wms_device_requests
WHERE business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND status_code = 'S'
AND device_status = 'S'
AND task_summary = 'Y';
SELECT mtl_material_transactions_s.NEXTVAL INTO l_txn_hdr_id FROM DUAL;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT task_id,lpn_id INTO l_txn_temp_id,l_orig_lpn_id
FROM wms_device_requests_hist
WHERE request_id = l_rec.relation_id
AND task_id = l_rec.task_id
AND business_event_id IN (wms_device_integration_pvt.wms_be_pick_release,wms_device_integration_pvt.wms_be_wip_pick_release,wms_device_integration_pvt.wms_be_mo_task_alloc)
AND ROWNUM <2;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT
transaction_header_id,
inventory_item_id,
move_order_line_id,
primary_quantity,
transaction_quantity,
transfer_subinventory,
transfer_to_location,
revision,
transaction_source_type_id,
transaction_action_id,
subinventory_code,
locator_id,
last_updated_by,
transaction_uom,
transaction_type_id,
cost_group_id
INTO
l_orig_txn_hdr_id,
l_inventory_item_id,
l_move_order_line_id,
l_pr_qty,
l_mmtt_txn_qty,
l_xfer_sub_code,
l_xfer_loc_id,
l_rev,
l_tran_source_type_id,
l_tran_action_id,
l_sub_code,
l_loc_id,
l_last_updated_by,
l_transaction_uom,
l_tran_type_id,
l_cost_group_id
FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT
msi.lpn_controlled_flag
INTO
l_lpn_controlled_flag
FROM
mtl_secondary_inventories msi
WHERE msi.organization_id = l_rec.organization_id
AND msi.secondary_inventory_name = l_rec.transfer_sub_code
AND sysdate <= nvl(msi.disable_date,sysdate);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT 1 INTO l_count FROM DUAL WHERE exists
( SELECT 1
FROM mtl_item_locations_kfv
WHERE organization_id = l_rec.organization_id
AND inventory_location_id = l_rec.transfer_loc_id
AND sysdate < nvl(disable_date,sysdate+1)
);
--update wdr for error_code and mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lpn_context
INTO l_pick_lpn_context
FROM wms_license_plate_numbers WHERE
lpn_id = l_rec.lpn_id
AND organization_id = l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lpn_context,license_plate_number
INTO l_lpn_context, l_xfer_lpn
FROM wms_license_plate_numbers WHERE
lpn_id = l_rec.xfer_lpn_id
AND organization_id = l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT lot_control_code,serial_number_control_code,primary_uom_code
INTO l_lot_code,l_serial_code,l_primary_uom
FROM mtl_system_items
WHERE organization_id = l_rec.organization_id
AND inventory_item_id = l_inventory_item_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT bremp.resource_id role_id
, t.wms_task_type
, t.standard_operation_id
, t.operation_plan_id
INTO l_per_res_id
, l_wms_task_type
, l_std_op_id
, l_operation_plan_id
FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
WHERE t.transaction_temp_id = l_rec.task_id
AND t.standard_operation_id = bsor.standard_operation_id
AND bsor.resource_id = bremp.resource_id
AND bremp.resource_type = 2
AND ROWNUM < 2;
SELECT employee_id INTO l_person_id
FROM fnd_user WHERE user_id = fnd_global.user_id;
INSERT INTO wms_dispatched_tasks
(
task_id
, transaction_temp_id
, organization_id
, user_task_type
, person_id
, effective_start_date
, effective_end_date
, person_resource_id
, status
, dispatched_time
, last_update_date
, last_updated_by
, creation_date
, created_by
, task_type
, operation_plan_id
, move_order_line_id
)
VALUES (
wms_dispatched_tasks_s.NEXTVAL
, l_rec.task_id --transaction_temp_id
, l_rec.organization_id
, NVL(l_std_op_id, 2)
, l_person_id
, SYSDATE
, SYSDATE
, l_per_res_id
, l_g_task_active
, SYSDATE
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, l_wms_task_type
, l_operation_plan_id
, l_move_order_line_id
);
--Update the request_id for child records if they exist.
IF (l_lot_code >1 OR l_serial_code >1) THEN--LOT OR/AND SERIAL ITEMS
IF (l_debug = 1) THEN
trace('device_confirmation:validating lot/serial substitution');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
-- Later update LPN context For this case of picked_from_LPN to Packing
-- context AS it will be entirely nested
ELSIF l_lpn_match=2 THEN
if l_rec.lpn_id = l_rec.xfer_lpn_id then
--Error
IF (l_debug = 1) THEN
trace('error out,Can not move the entire LPN');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--Update MTLT/MSNT
IF (l_qty_discrepancy_flag <> 0) AND l_rec.transaction_quantity <> 0 THEN --means qty_discrepancy
--we do not want to error out in case qty_picked is 0 and
--lot/serial info in child record is NOT provided
IF (l_lot_code >1 OR l_serial_code >1) THEN --LOT OR/AND SERIAL ITEMS
IF (l_debug = 1) THEN
trace('device_confirmation:validating lot/serial substitution');
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT COUNT(*)
INTO l_mmtt_count
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id <> l_rec.task_id
AND mmtt.move_order_line_id = l_move_order_line_id;
SELECT mtrl.txn_source_line_id
INTO l_source_line_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = l_move_order_line_id;
trace('Other MMTT lines exist too. Delete MMTT and UPDATE move ORDER line');
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id);
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id;
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_detailed - l_pr_qty-- this diff is zero
WHERE line_id = l_move_order_line_id;
DELETE FROM wms_dispatched_tasks WHERE transaction_temp_id = l_rec.task_id; --Bug 6987801
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
DELETE FROM mtl_material_transactions_temp
WHERE transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id IN
(SELECT mtlt.serial_transaction_temp_id
FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id);
DELETE FROM mtl_transaction_lots_temp mtlt
WHERE mtlt.transaction_temp_id = l_txn_temp_id;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id;
-- Update the context to whatever it started with
-- before processing the record with zero qty
--
/* updated as part of the bug 4411819
--using wms_container_pvt.Modify_LPN() API instead
UPDATE wms_license_plate_numbers
SET lpn_context = l_lpn_context -- this still has
--the original value of lpn context
WHERE lpn_id = l_rec.xfer_lpn_id;
-- Clean up code. Have to delete MMTT, MTLT, MSNT, WDT, if picked less
-- and update move order line
IF (l_debug = 1) THEN
trace('Deleteing all unpicked lot/serials from MTLT/MSNT');
--delete all unpicked lot/serials from MTLT/MSNT
IF l_lot_code >1 THEN
IF (l_serial_code >1 AND l_serial_code<>6) THEN
DELETE FROM mtl_serial_numbers_temp msnt
WHERE transaction_temp_id IN
(SELECT msnt.transaction_temp_id
FROM mtl_transaction_lots_temp mtlt,
mtl_serial_numbers_temp msnt
WHERE mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
AND mtlt.transaction_temp_id =l_txn_temp_id)
AND msnt.fm_serial_number NOT IN
(SELECT serial_number FROM wms_device_requests
WHERE relation_id = l_rec.relation_id
AND task_id = l_txn_temp_id
AND business_event_id = wms_device_integration_pvt.WMS_BE_TASK_CONFIRM
AND task_summary = 'N');
--Update qty in MTLT by qty passed in the child
-- records for corresponding lots
--In the child record, txn quantity is total
--quantity of the parent record. It is the
--lot_qty column which keeps correct lot qty for child record.
for l_mtlt_update in c_mtlt_update(l_rec.relation_id,l_txn_temp_id)
loop
l_mtlt_pr_qty :=l_mtlt_update.lot_qty;
from_quantity => l_mtlt_update.lot_qty,
from_unit => l_transaction_uom,
to_unit => l_primary_uom,
from_name => null,
to_name => null);
trace('l_mtlt_pr_qty::'||l_mtlt_pr_qty||'mtlt_lot_TXN_QTY:::'||l_mtlt_update.lot_qty);
update mtl_transaction_lots_temp set
TRANSACTION_QUANTITY = l_mtlt_update.lot_qty,
PRIMARY_QUANTITY = l_mtlt_pr_qty
WHERE transaction_temp_id = l_txn_temp_id
AND lot_number = l_mtlt_update.lot_number;
DELETE FROM mtl_serial_numbers_temp msnt
WHERE msnt.transaction_temp_id = l_txn_temp_id
AND msnt.fm_serial_number NOT IN
( SELECT wdr.serial_number FROM
wms_device_requests wdr
WHERE relation_id = l_rec.relation_id
AND task_id = l_txn_temp_id
AND task_summary = 'N'
AND business_event_id IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.WMS_BE_load_CONFIRM));
UPDATE mtl_txn_request_lines
SET quantity_detailed = l_rec.transaction_quantity
WHERE line_id = l_move_order_line_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET content_lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET lpn_id = l_rec.lpn_id,
transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
UPDATE mtl_material_transactions_temp
SET transfer_lpn_id = l_rec.xfer_lpn_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id;
--update MMTT for qty/loc/sub disc
ll_pr_qty := l_pr_qty ;
UPDATE mtl_material_transactions_temp
SET transfer_subinventory = ll_xfer_sub_code
, transfer_to_location = ll_xfer_loc_id
, primary_quantity = ll_pr_qty
, transaction_quantity = ll_mmtt_txn_qty
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id = l_rec.organization_id;
, p_user_id =>l_last_updated_by--from mmtt
, p_item_id =>l_inventory_item_id
, p_is_loc_desc =>TRUE --Bug 4319541
);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
SELECT 1
INTO l_wf
FROM MTL_TRANSACTION_REASONS
WHERE reason_id=l_rec.reason_id
and workflow_name is not null
and workflow_name<>' '
and workflow_process is not null
and workflow_process<>' ';
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE mtl_material_transactions_temp
SET transaction_date = sysdate
,transaction_status = 3
,transaction_header_id = l_txn_hdr_id
WHERE transaction_temp_id = l_txn_temp_id
AND organization_id= l_rec.organization_id
AND l_rec.business_event_id <> wms_device_integration_pvt.wms_be_load_confirm;
UPDATE wms_dispatched_tasks
SET status = l_g_task_loaded
,last_update_date = Sysdate
,loaded_time = Sysdate
,last_updated_by = fnd_global.user_id
WHERE transaction_temp_id = l_rec.task_id;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
--update wdr for error_code and error_mesg
ROLLBACK TO WMS_DEVICE_REQUESTS_SP;
update_wdr_for_error_rec(l_rec.task_id,l_rec.relation_id);
UPDATE wms_device_requests
SET status_code = 'S',
status_msg = null
WHERE business_event_id in (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm)
AND task_id = l_rec.task_id
AND relation_id = l_rec.relation_id;
trace('Need to update the account period in MMTT');
UPDATE mtl_material_transactions_temp
SET acct_period_id = l_period_id
WHERE transaction_temp_id = l_open_period_check.task_id
AND organization_id = l_open_period_check.organization_id;
trace('Done with all records: Insert WDT History ONLY for LPNs to be DROPPED');
UPDATE mtl_material_transactions_temp mmtt
set transfer_lpn_id = NULL , lpn_id = NULL
WHERE mmtt.transaction_temp_id = l_mmtt_csr.transaction_temp_id;
, p_delete_mmtt_flag => 'N'
, p_txn_header_id => l_txn_hdr_id
, p_transfer_lpn_id => NVL(l_mmtt_csr.transfer_lpn_id, l_mmtt_csr.content_lpn_id)
);
--Step6 Update LPN Context apporpriately for processed LPNs
--For droped LPN tasks ONLY:
-- Replenishment task = (reside IN inv) wms_container_pub.lpn_context_inv
-- so taks = Handled by TM (Do NOT do anything)
-- All successful LPNs are already Loaded in the loop above for each call
--Update the LPN context to Reside in INV for all LPNs for Replenishment
--tasks. For SO tasks, it is handled in the TM
-- the cursor c_update_xfer_lpns_context ensure that LPNs for
-- replenishment tasks are here only
FOR l_update_xfer_lpns_context IN c_update_xfer_lpns_context loop
--In case LPN is getting transferred, both lpn_id and
--xfer_lpn_id are same, so getting updated correctly
--as part of the bug 4411819, replaced
--wms_container_pub.modify_lpn_wrapper to wms_container_pvt.Modify_LPN
l_lpn.lpn_id := l_update_xfer_lpns_context.lpn_id;
l_lpn.organization_id := l_update_xfer_lpns_context.organization_id;
trace(' device_confirmation:Delete requested rows from WDR');
delete from wms_device_requests;--since temp table is session specific
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = sysdate,
status_msg= 'g_expected_error'
WHERE request_id = l_new_request_id
AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);
UPDATE wms_device_requests_hist
SET status_code = 'E',resubmit_date = Sysdate,
status_msg='g_unexpected_error'
WHERE request_id = l_new_request_id
AND BUSINESS_EVENT_ID IN (wms_device_integration_pvt.wms_be_task_confirm,wms_device_integration_pvt.wms_be_load_confirm);