[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => x_return_status
);
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
l_shipping_attr.DELETE;
all_del_det_bo_tbl.DELETE;
SELECT mmtt.transaction_temp_id
, mmtt.subinventory_code
, mmtt.locator_id
, mmtt.transfer_to_location
, mmtt.organization_id
, wdd.oe_header_id
, wdd.oe_line_id
, wdd.customer_id
, wdd.freight_code
, wdd.ship_to_location
, wdd.shipment_priority_code
, wdd.trip_stop_id
, wdd.shipping_delivery_id
, mol.ship_set_id
, mol.ship_model_id
, mmtt.parent_line_id
, mmtt.transfer_subinventory
, mmtt.project_id
, mmtt.task_id
, mmtt.inventory_item_id
, mmtt.revision
FROM mtl_material_transactions_temp mmtt,mtl_txn_request_lines mol,wsh_inv_delivery_details_v wdd
WHERE mmtt.move_order_line_id = mol.line_id
AND mol.header_id = p_move_order_header_id
AND wdd.move_order_line_id = mol.line_id
AND mmtt.pick_slip_number IS NULL;
SELECT wct.transaction_temp_id
FROM wms_cartonization_temp wct
WHERE wct.parent_line_id = wct.transaction_temp_id; -- only parent lines
UPDATE mtl_material_transactions_temp
SET pick_slip_number = wsh_pick_slip_numbers_s.nextval
WHERE transaction_temp_id = mmtt_line.transaction_temp_id;
SELECT grouping_rule_id,organization_id
INTO l_grouping_rule_id,l_organization_id
FROM mtl_txn_request_headers
WHERE header_id = p_move_order_header_id;
SELECT pick_grouping_rule_id
INTO l_grouping_rule_id
FROM wsh_shipping_parameters -- Bug 16309114 changed from wsh_parameters
WHERE organization_id = l_organization_id;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = wsh_pick_slip_numbers_s.nextval
WHERE transaction_temp_id = mmtt_line.parent_line_id;
SELECT request_number
INTO l_request_number
FROM mtl_txn_request_headers
WHERE header_id = p_move_order_header_id;
SELECT document_set_id
INTO l_report_set_id
FROM wsh_picking_batches
WHERE NAME = l_request_number;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = l_pick_slip_number
, transaction_source_id = l_mso_header_id
, trx_source_line_id = mmtt_line.oe_line_id
, demand_source_header_id = l_mso_header_id
, demand_source_line = mmtt_line.oe_line_id
WHERE transaction_temp_id = mmtt_line.transaction_temp_id;
SELECT request_number
INTO l_request_number
FROM mtl_txn_request_headers
WHERE header_id = p_move_order_header_id;
SELECT document_set_id
INTO l_report_set_id
FROM wsh_picking_batches
WHERE NAME = l_request_number;
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.move_order_header_id = p_mo_hdr_id
AND mmtt.parent_line_id IS NULL
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta
WHERE wutta.organization_id = mmtt.organization_id
AND wutta.user_task_type_id = mmtt.standard_operation_id
AND wutta.honor_case_pick_flag = 'Y'
)
UNION ALL
-- Bulk pick parent tasks
SELECT mmtt.transaction_temp_id
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.transaction_temp_id IN
( SELECT DISTINCT mmtt2.parent_line_id
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.move_order_header_id = p_mo_hdr_id
AND mmtt2.parent_line_id IS NOT NULL
AND mmtt2.transaction_temp_id <> mmtt2.parent_line_id
)
AND EXISTS
( SELECT 'x'
FROM wms_user_task_type_attributes wutta
WHERE wutta.organization_id = mmtt.organization_id
AND wutta.user_task_type_id = mmtt.standard_operation_id
AND wutta.honor_case_pick_flag = 'Y'
);
print_debug('Item is unit effective. Inserting new line rec','PICKREL');
SELECT revision_qty_control_code, lot_control_code,
primary_uom_code, NVL(reservable_type,1)
INTO l_revision_control_code, l_lot_control_code,
l_primary_uom_tbl(l_item_index),
l_reservable_type
FROM mtl_system_items
WHERE organization_id = l_organization_id
AND inventory_item_id = l_item_index;
SELECT SOURCE_HEADER_ID
INTO l_OE_HEADER_ID
FROM wsh_delivery_details
WHERE move_order_line_id =
l_qtree_line_tbl(l_qtree_line_index).move_order_line_id
AND move_order_line_id is not NULL
AND released_status = 'S';
select t.transaction_source_type_id
into l_demand_source_type
from mtl_transaction_types t, mtl_txn_source_types st
where t.transaction_type_id =
l_qtree_line_tbl(l_qtree_line_index).transaction_type_id
and t.transaction_source_type_id = st.transaction_source_type_id;
SELECT print_pick_slip_mode, pick_grouping_rule_id
INTO l_print_mode, g_org_grouping_rule_id
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = l_organization_id;
l_wdd_index_tbl.DELETE;
print_debug('Before calling the update_wave_lines_tbl ','Inv_Pick_Release_Pub.Pick_Release');
wms_wave_planning_pvt.update_wave_lines_tbl( p_mo_line_table => l_mo_line_tbl
,x_return_status => l_return_status);
print_debug('After calling the update_wave_lines_tbl which has returned error - Still Continue with rest of Pick Release','Inv_Pick_Release_Pub.Pick_Release');
print_debug('After calling the update_wave_lines_tbl ','Inv_Pick_Release_Pub.Pick_Release');
SELECT pick_grouping_rule_id
INTO l_grouping_rule_id
FROM wsh_shipping_parameters
WHERE organization_id = l_organization_id;
l_qtree_backup_tbl.DELETE;
l_qtree_backup_tbl.DELETE;
l_qtree_backup_tbl.DELETE;
l_qtree_backup_tbl.DELETE;
x_pick_release_status.delete;
--select nvl(sum(transaction_quantity),0)
--into l_transaction_quantity
--from mtl_material_transactions_Temp
--where move_order_line_id = l_mo_line.line_id;
-- Update the move order line to change the requested quantity
-- to be equal to the allocated quantity
-- Get the tolerance set while allocating the line
-- If quantity is within tolerance then do not backorder shipset
--
-- l_lower_tolerance := l_quantity * inv_pick_release_pvt.g_min_tolerance;
print_debug('Update shipping that ship set detailing failed',
'Inv_Pick_Release_Pub.Pick_Release');
--delete entry, so we don't restore tree more than once
l_qtree_backup_tbl.DELETE(l_tree_id);
--Call Update_Shipping_Attributes to backorder detail line
l_shipping_attr(1).source_header_id := l_source_header_id;
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_api_return_status
);
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
-- HW INVCONV Update Qty2
update mtl_txn_request_lines
set quantity = 0
,quantity_detailed = 0
,secondary_quantity = decode(secondary_quantity,fnd_api.g_miss_num, NULL, 0)
,secondary_quantity_detailed = decode(secondary_quantity_detailed,fnd_api.g_miss_num, NULL, 0)
,line_status = 5
,status_date =sysdate --BUG 7560455
where line_id = l_mo_line.line_id;
-- for the last line. The table gets updated for the last
-- line later.
-- l_set_index should always be equal to the last line
-- in the current ship set, so that the logic at the
-- end of the outer loop works correctly.
EXIT WHEN l_mo_line_tbl.LAST = l_set_index;
--If next line is for same ship set, update output table
l_set_process := l_set_process + 1;
l_qtree_backup_tbl.DELETE;
print_debug('Update shipping that ship model detailing partial',
'Inv_Pick_Release_Pub.Pick_Release');
SELECT ordered_quantity, order_quantity_uom
INTO l_cur_txn_source_req_qty, l_txn_source_line_uom
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_cur_txn_source_line_id;
--delete entry, so we don't restore tree more than once
l_qtree_backup_tbl.DELETE(l_tree_id);
SELECT ordered_quantity, order_quantity_uom
INTO l_set_txn_source_req_qty, l_txn_source_line_uom
FROM OE_ORDER_LINES_ALL
WHERE line_id = l_set_txn_source_line_id;
--Call Update_Shipping_Attributes to backorder detail line
l_shipping_attr(1).source_header_id := l_source_header_id;
l_smc_backorder_det_tbl.DELETE;
l_smc_backorder_det_tbl.DELETE;
l_smc_backorder_det_tbl.DELETE;
--WSH_INTERFACE.Update_Shipping_Attributes
--(p_source_code => 'INV',
--p_changed_attributes => l_shipping_attr,
--x_return_status => l_api_return_status
--);
-- Update mo line with new quantity and model quantity;
update mtl_txn_request_lines
set quantity = 0
,quantity_detailed = 0
,line_status = 5
,status_date =sysdate --BUG 7560455
,model_quantity = l_new_model_quantity
where line_id = l_mo_line.line_id;
update mtl_txn_request_lines
set quantity = l_new_line_quantity
,quantity_detailed = NULL
,model_quantity = l_new_model_quantity
where line_id = l_mo_line.line_id;
-- for the last line. The table gets updated for the last
-- line later.
-- l_set_index should always be equal to the last line
-- in the current ship set, so that the logic at the
-- end of the outer loop works correctly.
EXIT WHEN l_mo_line_tbl.LAST = l_set_index;
-- Only update status table if model_quantity = 0;
-- We have to update the status now.
if l_new_model_quantity = 0 then
--If next line is for same ship set, update output table
l_set_process := l_set_process + 1;
l_qtree_backup_tbl.DELETE;
SELECT *
INTO INV_CACHE.wdd_rec
FROM WSH_DELIVERY_DETAILS
WHERE move_order_line_id = l_mo_line.line_id
AND NVL(released_status, 'Z') NOT IN ('Y','C');
-- new logic to support crossdocking. The WDD record needs to be split or updated
-- properly so we can still try to allocate material through crossdocking later.
-- {{
-- Run PR in Prioritize INV mode and ensure that one WDD
-- does not get allocated at all. That WDD should get
-- x-docked and later deliveries created for same.
-- }}
-- {{
-- Run PR in Prioritize INV mode and ensure that one WDD
-- gets partially allocated. That WDD should get split and
-- x-docked and later deliveries created for the new WDD.
-- Also ensure that the original WDD has the correct qty.
-- }}
IF (l_allocation_method = g_prioritize_inventory) AND (p_wsh_release_table.COUNT > 0) THEN
IF (l_transaction_quantity = 0) THEN
-- Move order line is not allocated at all.
-- Do not backorder the current WDD line yet since crossdocking can still
-- potentially allocate material for this. Update the WDD record to null
-- out the move_order_line_id column and reset the released_status to the
-- original value from the corresponding record in p_wsh_release_table.
-- R12.1 replenishment Project 6681109/6710368
-- changes based ON p_dynamic_replenishment
IF (is_debug) THEN
print_debug('p_dynamic_replenishment :'||p_dynamic_replenishment,
'INV_Pick_Release_Pub.Pick_Release');
l_in_rec.action_code := 'UPDATE';
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail API',
'Inv_Pick_Release_Pub.Pick_Release');
print_debug('Unexpected errror from Create_Update_Delivery_Detail API',
'Inv_Pick_Release_Pub.Pick_Release');
-- Update the split WDD line for the unallocated quantity to null out the
-- move_order_line_id column and reset the released_status to the original
-- value in the corresponding WDD record (original one) in p_wsh_release_table
l_detail_info_tab(1).delivery_detail_id := l_split_delivery_detail_id;
l_in_rec.action_code := 'UPDATE';
WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail
(p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_detail_info_tab => l_detail_info_tab,
p_in_rec => l_in_rec,
x_out_rec => l_out_rec
);
print_debug('Error returned from Create_Update_Delivery_Detail API',
'Inv_Pick_Release_Pub.Pick_Release');
print_debug('Unexpected errror from Create_Update_Delivery_Detail API',
'Inv_Pick_Release_Pub.Pick_Release');
-- Insert the split WDD line into p_wsh_release_table.
-- The split WDD release record should be the same as the original one with
-- only the following fields modified: delivery_detail_id, move_order_line_id
-- replenishment_status, and requested_quantity fields
l_split_wdd_rel_rec := p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id));
-- Insert a new record into p_trolin_delivery_ids and p_del_detail_id
-- for the split WDD line created.
-- UPDATE: Do not need to do this anymore. The delivery tables passed in by
-- Shipping are used for storing crossdocked WDD lines. If this split line
-- is later allocated from Crossdocking, the crossdock API will insert them
-- into the delivery tables.
/*l_xdock_index := NVL(p_del_detail_id.LAST, 0) + 1;
-- Update the original WDD line in p_wsh_release_table with
-- released_status = 'S' and the corresponding allocated quantity
l_xdock_index := l_wdd_index_tbl(l_delivery_detail_id);
select count(1) INTO l_reservation_exists
from mtl_reservations mr
WHERE MR.DEMAND_SOURCE_LINE_ID = l_source_line_id
and MR.DEMAND_SOURCE_HEADER_ID =
inv_salesorder.get_salesorder_for_oeheader(l_source_header_id)
and MR.demand_source_type_id = decode(INV_CACHE.wdd_rec.source_document_type_id, 10, 8, 2)
and MR.SUBINVENTORY_CODE IS NOT NULL --locator is not needed
and (nvl(mr.staged_flag,'N') = 'N' and nvl(mr.detailed_quantity,0) = 0);
--Note: Inside the API Update_Shipping_Attributes, wdd is split and qty are backordered
--in case action_flag is 'B'. Backorder qty is passed
--from INV as cycle_count_quantity below and requested qty is obtained from WDD table by
--shipping. Now in case of 'R', shipping team will make change
-- to mark those lines as Replenishment Requested instead of backordering them
IF (l_reservation_exists = 0 and NVL(p_dynamic_replenishment,'N') = 'Y') THEN --BUG13604664
IF is_debug THEN
print_debug('Marking line status as RR',
'Inv_Pick_Release_Pub.Pick_Release');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_api_return_status
);
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
print_debug('return error from update shipping attributes',
'Inv_Pick_Release_Pub.Pick_Release');
-- Update the current move order line depending on how much quantity
-- was successfully allocated from inventory.
IF (l_transaction_quantity = 0) THEN
-- Close the move order line created since no quantity was allocated
UPDATE mtl_txn_request_lines
SET line_status = 5,
status_date =sysdate, --BUG 7560455
quantity = l_transaction_quantity,
secondary_quantity = DECODE(secondary_quantity, fnd_api.g_miss_num, NULL,
l_transaction_quantity2)
WHERE line_id = l_mo_line.line_id;
-- UPDATE: Do not need to do this anymore. The delivery tables inputted from
-- Shipping are used only to store crossdocked WDD lines. They will be
-- empty initially. Instead, set the released_status for the line to be 'B'
-- in the inputted release table.
--l_backordered_wdd_tbl(l_delivery_detail_id) := TRUE;
-- Update the move order line to the partial quantity that was allocated
UPDATE mtl_txn_request_lines
SET quantity = l_transaction_quantity,
secondary_quantity = DECODE(secondary_quantity, fnd_api.g_miss_num, NULL,
l_transaction_quantity2)
,primary_quantity=l_transaction_quantity
WHERE line_id = l_mo_line.line_id;
-- Bug# 4258360: If allocation mode = N (Prioritize Inventory), we need to update
-- the corresponding WDD record in p_wsh_release_table to a released_status of 'S'.
-- This is so the crossdock API (which will be called later on) knows the WDD record has
-- been fully allocated already. This is added for the R12 Planned Crossdocking project.
-- {{
-- Run PR in prioritize INV mode and ensure entire WDD
-- gets allocated. That WDD should not be re-allocated for
-- x-docking.
-- }}
IF (l_allocation_method = g_prioritize_inventory) THEN
-- Retrieve the WDD record associated with the current MOL
IF (NOT INV_CACHE.set_wdd_rec(l_mo_line.line_id)) THEN
IF (is_debug) THEN
print_debug('Error setting cache for WDD delivery line',
'INV_Pick_Release_Pub.Pick_Release');
-- Update WDD record in release table with a released status of 'S'
IF (p_wsh_release_table.COUNT > 0) THEN
p_wsh_release_table(l_wdd_index_tbl(l_delivery_detail_id)).released_status := 'S';
update mtl_material_transactions_temp
set lock_flag = 'Y'
where move_order_line_id =l_mol_id_tbl(ii);
DELETE FROM mtl_txn_request_lines mtrl
WHERE line_status = 5
AND line_id = l_mol_id_tbl(ii)
AND EXISTS
( SELECT 'x'
FROM mtl_system_items msi
WHERE msi.organization_id = mtrl.organization_id
AND msi.inventory_item_id = mtrl.inventory_item_id
AND NVL(msi.reservable_type,1) = 1
);
p_del_detail_id.DELETE(l_xdock_index);
p_trolin_delivery_ids.DELETE(l_xdock_index);
-- to or updated
l_mso_header_id NUMBER; -- The header ID for the record in
SELECT print_pick_slip_mode, pick_grouping_rule_id
INTO l_print_mode, g_org_grouping_rule_id
FROM WSH_SHIPPING_PARAMETERS
WHERE organization_id = p_org_id;
update mtl_material_transactions_temp
set lock_flag = NULL
where move_order_header_id = p_move_order_header_id
and organization_id = p_org_id
and lock_flag is not null;
-- to or updated
l_mso_header_id NUMBER; -- The header ID for the record in