[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_detail_temp_records
( x_return_status OUT NOCOPY VARCHAR2
, p_wms_txn_temp_tbl IN g_wms_txn_temp_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'insert_detail_temp_records';
SELECT wms_transactions_temp_s.NEXTVAL
FROM DUAL;
log_procedure(l_api_name, 'start', 'Start Insert_Detail_temp_records');
INSERT INTO wms_transactions_temp
(
pp_transaction_temp_id
, transaction_temp_id
, type_code
, line_type_code
, transaction_quantity
, primary_quantity
, secondary_quantity
, grade_code
, revision
, lot_number
, lot_expiration_date
, serial_number
, from_subinventory_code
, from_locator_id
, rule_id
, reservation_id
, to_subinventory_code
, to_locator_id
, from_organization_id
, to_organization_id
, from_cost_group_id
, to_cost_group_id
, lpn_id
)
VALUES (
wms_transactions_temp_s.NEXTVAL
-- l_temp_id_tbl(l_counter)
, p_wms_txn_temp_tbl(l_counter).transaction_temp_id
, p_wms_txn_temp_tbl(l_counter).type_code
, p_wms_txn_temp_tbl(l_counter).line_type_code
, p_wms_txn_temp_tbl(l_counter).transaction_quantity
, p_wms_txn_temp_tbl(l_counter).primary_quantity
, p_wms_txn_temp_tbl(l_counter).secondary_quantity
, p_wms_txn_temp_tbl(l_counter).grade_code
, p_wms_txn_temp_tbl(l_counter).revision
, p_wms_txn_temp_tbl(l_counter).lot_number
, p_wms_txn_temp_tbl(l_counter).lot_expiration_date
, p_wms_txn_temp_tbl(l_counter).serial_number
, p_wms_txn_temp_tbl(l_counter).from_subinventory_code
, p_wms_txn_temp_tbl(l_counter).from_locator_id
, p_wms_txn_temp_tbl(l_counter).rule_id
, p_wms_txn_temp_tbl(l_counter).reservation_id
, p_wms_txn_temp_tbl(l_counter).to_subinventory_code
, p_wms_txn_temp_tbl(l_counter).to_locator_id
, p_wms_txn_temp_tbl(l_counter).from_organization_id
, p_wms_txn_temp_tbl(l_counter).to_organization_id
, p_wms_txn_temp_tbl(l_counter).from_cost_group_id
, p_wms_txn_temp_tbl(l_counter).to_cost_group_id
, p_wms_txn_temp_tbl(l_counter).lpn_id
);
log_event(l_api_name, 'detail temp insert ', 'detail temp insert '
|| p_wms_txn_temp_tbl(l_counter).secondary_quantity);
log_procedure(l_api_name, 'end', 'End Insert_Detail_temp_records');
log_error(l_api_name, 'error', 'Error in Insert_Detail_temp_records');
'Unexpected error in Insert_Detail_temp_records');
'Other error in Insert_Detail_temp_records');
END insert_detail_temp_records;
DELETE FROM wms_transactions_temp
WHERE transaction_temp_id = p_request_line_rec.line_id;
DELETE FROM wms_txn_context_temp
WHERE line_id = p_request_line_rec.line_id;
insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
SELECT SUM(wtt.transaction_quantity) transaction_quantity
, SUM(wtt.primary_quantity) primary_quantity
, SUM(wtt.secondary_quantity) secondary_quantity
, wtt.grade_code grade_code
, wtt.revision revision
, wtt.lot_number lot_number
, wtt.from_subinventory_code from_subinventory_code
, wtt.from_locator_id from_locator_id
, wtt.from_cost_group_id from_cost_group_id
, wtt.lpn_id lpn_id
FROM wms_transactions_temp wtt
WHERE wtt.transaction_temp_id = p_request_line_rec.line_id
AND wtt.line_type_code = 2 -- output line
AND wtt.type_code = 2 -- pick
GROUP BY wtt.lot_number
, wtt.revision
, wtt.from_subinventory_code
, wtt.from_locator_id
, wtt.from_cost_group_id
, wtt.lpn_id
, wtt.grade_code
ORDER BY MIN(wtt.pp_transaction_temp_id);
insert_detail_temp_records(l_return_status, l_wms_txn_temp_tbl);
SELECT transaction_temp_id
, line_type_code
, type_code
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id
, primary_quantity
, transaction_quantity
, secondary_quantity
, grade_code
, reservation_id
, to_subinventory_code
, to_locator_id
, lpn_id
FROM wms_transactions_temp
ORDER BY transaction_temp_id
, line_type_code
, type_code
, revision
, lot_number
, lot_expiration_date
, from_subinventory_code
, from_locator_id;
SELECT x.revision
, x.from_subinventory_code
, x.from_locator_id
, x.to_subinventory_code
, x.to_locator_id
, x.lot_number
, MAX(x.lot_expiration_date) lot_expiration_date
, x.serial_number serial_number_start
, x.serial_number serial_number_end
, SUM(x.transaction_quantity) transaction_quantity
, SUM(x.primary_quantity) primary_quantity
, SUM(x.secondary_quantity) secondary_quantity
, grade_code
, MIN(x.pick_rule_id) pick_rule_id
, MIN(x.put_away_rule_id) put_away_rule_id
, x.reservation_id reservation_id
, x.from_cost_group_id
, x.to_cost_group_id
, x.lpn_id
FROM (SELECT wtt.revision
, wtt.from_subinventory_code
, wtt.from_locator_id
, wtt.to_subinventory_code
, wtt.to_locator_id
, wtt.lot_number
, wtt.lot_expiration_date
, wtt.serial_number
, wtt.transaction_quantity
, wtt.primary_quantity
, wtt.secondary_quantity
, wtt.grade_code
, DECODE(wtt.type_code, 2, wtt.rule_id, NULL) pick_rule_id
, DECODE(wtt.type_code, 1, wtt.rule_id, NULL) put_away_rule_id
, DECODE(wtt.type_code, 2, wtt.reservation_id, NULL) reservation_id
, wtt.pp_transaction_temp_id
, wtt.from_cost_group_id
, wtt.to_cost_group_id
, wtt.lpn_id
FROM wms_transactions_temp wtt
WHERE wtt.transaction_temp_id = l_transaction_temp_id
AND wtt.line_type_code = 2 -- output line
) x
GROUP BY x.revision
, x.from_subinventory_code
, x.to_subinventory_code
, x.from_locator_id
, x.to_locator_id
, x.from_cost_group_id
, x.to_cost_group_id
, x.lot_number
, x.serial_number
, x.reservation_id
, x.lpn_id
, x.grade_code
ORDER BY MIN(x.pp_transaction_temp_id);
SELECT revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, to_subinventory_code
, to_locator_id
, to_cost_group_id
, lot_number
, MAX(lot_expiration_date) lot_expiration_date
, serial_number
, SUM(transaction_quantity) transaction_quantity
, SUM(primary_quantity) primary_quantity
, SUM(secondary_quantity) secondary_quantity
, grade_code
, MIN(rule_id) pick_rule_id
, reservation_id
, lpn_id
FROM wms_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id
AND line_type_code = 2 -- output line
AND type_code = 2 -- pick
GROUP BY serial_number
, lot_number
, revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, reservation_id
, to_subinventory_code
, to_locator_id
, to_cost_group_id
, lpn_id
, grade_code
ORDER BY revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, lpn_id
, lot_number
, reservation_id --15955427
, serial_number
;
SELECT revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, to_subinventory_code
, to_locator_id
, to_cost_group_id
, lot_number
, MAX(lot_expiration_date) lot_expiration_date
, serial_number
, SUM(transaction_quantity) transaction_quantity
, SUM(primary_quantity) primary_quantity
, SUM(secondary_quantity) secondary_quantity
, grade_code
, MIN(rule_id) put_away_rule_id
, NULL reservation_id
, lpn_id lpn_id
FROM wms_transactions_temp
WHERE transaction_temp_id = l_transaction_temp_id
AND line_type_code = 2 -- output line
AND type_code = 1 -- put away
GROUP BY serial_number
, lot_number
, revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, to_subinventory_code
, to_locator_id
, to_cost_group_id
, lpn_id
, grade_code
ORDER BY revision
, from_subinventory_code
, from_locator_id
, from_cost_group_id
, lpn_id
, lot_number
, reservation_id --15955427
, serial_number
;
SELECT move_order_type
FROM mtl_txn_request_headers
WHERE header_id = l_request_line_rec.header_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_request_line_rec.from_subinventory_code
AND organization_id = l_request_line_rec.organization_id;
SELECT locator_type
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = l_request_line_rec.to_subinventory_code
AND organization_id = l_request_line_rec.to_organization_id;
SELECT location_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_request_line_rec.inventory_item_id
AND organization_id = l_request_line_rec.organization_id;
SELECT location_control_code
FROM mtl_system_items
WHERE inventory_item_id = l_request_line_rec.inventory_item_id
AND organization_id = l_request_line_rec.to_organization_id;
SELECT type_code
FROM wms_rules_b
WHERE rule_id = p_simulation_id;
SELECT type_code
FROM wms_strategies_b
WHERE strategy_id = p_simulation_id;
SELECT allow_cross_proj_issues
, allow_cross_unitnum_issues
FROM pjm_org_parameters
WHERE organization_id = l_request_line_rec.organization_id;
SELECT 1
FROM mtl_reservations
WHERE organization_id = l_request_line_rec.organization_id
AND supply_source_type_id = 5
AND supply_source_header_id = l_request_line_rec.txn_source_id;
SELECT stock_locator_control_code
FROM mtl_parameters
WHERE organization_id = l_request_line_rec.organization_id;
SELECT wms_transactions_temp_s.NEXTVAL
FROM DUAL;
SELECT entity_type
FROM wip_entities
WHERE wip_entity_id = l_request_line_rec.txn_source_id
AND organization_id = l_request_line_rec.organization_id;
SELECT uom_class
FROM mtl_units_of_measure_vl
WHERE uom_code = p_uom;
/* Fix for Bug#8355668. Remove existing elements for Select Available Inventory form */
if (p_simulation_mode = 10 ) then
WMS_SEARCH_ORDER_GLOBALS_PVT.g_available_inv_tbl.DELETE ;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers wlpn
WHERE lpn_id = (SELECT lpn_id
FROM mtl_txn_request_lines mtrl
WHERE mtrl.line_id = p_transaction_temp_id)
AND EXISTS(SELECT 1 FROM wms_lpn_contents wlc, mtl_txn_request_lines mtrl -- for bug 14035958
WHERE wlc.parent_lpn_id=mtrl.lpn_id
AND mtrl.line_id = p_transaction_temp_id
AND wlc.parent_lpn_id=wlpn.lpn_id
And wlc.inventory_item_id=mtrl.inventory_item_id
AND (wlc.lot_number IS NULL OR wlc.lot_number=mtrl.lot_number) --if no lot infor in mtrl, don't quick pick
AND (wlc.revision IS NULL OR wlc.revision=mtrl.revision) --if no revision infor in mtrl, don't quick pick
AND (wlc.serial_summary_entry <>1 OR (mtrl.serial_number_start IS NOT NULL AND mtrl.serial_number_end IS NOT NULL)) --if no serial infor in mtrl, don't quick pick
);
validate_and_insert_noqtytree() will be created to insert data in WTT */
l_quick_pick_flag := 'N';
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = l_request_line_rec.lpn_id ; /* (SELECT lpn_id
INSERT INTO wms_txn_context_temp
(
line_id
, txn_source_id
, txn_source_line_id
, txn_source_name
, txn_source_line_detail
, freight_carrier_code
, customer_id
)
VALUES (
l_request_line_rec.line_id
, l_request_context.txn_header_id
, l_request_context.txn_line_id
, NULL
, l_request_context.txn_line_detail
, l_request_context.freight_code
, l_request_context.customer_id
);
DELETE FROM wms_transactions_temp
WHERE transaction_temp_id = p_transaction_temp_id
AND line_type_code = 1;
log_event(l_api_name, '','insert into wtt '||l_pp_transaction_temp_id);
INSERT INTO wms_transactions_temp
(
pp_transaction_temp_id
, transaction_temp_id -- mo_line_id
, type_code -- mo
, line_type_code -- 1
, transaction_quantity
, primary_quantity
, secondary_quantity
)
VALUES ( wms_transactions_temp_s.NEXTVAL
-- l_pp_transaction_temp_id
, l_request_line_rec.line_id
, 2
, 1
, l_request_line_rec.quantity
, l_request_line_rec.quantity
, l_request_line_rec.secondary_quantity
);
log_procedure(l_api_name, 'insert_trace_header', 'Calling insert_trace_header ');
wms_search_order_globals_pvt.insert_trace_header(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_header_id => g_trace_header_id
, p_pick_header_id => g_trace_header_id
, p_move_order_line_id => p_transaction_temp_id
, p_total_qty => l_request_line_rec.quantity - l_request_line_rec.quantity_detailed
, p_secondary_total_qty => l_request_line_rec.secondary_quantity - l_request_line_rec.secondary_quantity_detailed
, p_type_code => l_type_code
, p_business_object_id => g_business_object_id
, p_object_id => l_sequence_number
, p_strategy_id => l_strategy_id
);
wms_rule_pvt.rollback_capacity_update(
x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => l_request_line_rec.organization_id
, p_inventory_item_id => l_request_line_rec.inventory_item_id
);
log_procedure(l_api_name, 'insert_txn_trace_rows',
'Calling insert_txn_trace_rows ');
wms_search_order_globals_pvt.insert_txn_trace_rows(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_validation_level => fnd_api.g_valid_level_full
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_txn_header_id => inv_detail_util_pvt.g_transaction_header_id
, p_insert_lot_flag => inv_detail_util_pvt.g_insert_lot_flag
, p_insert_serial_flag => inv_detail_util_pvt.g_insert_serial_flag
);
inv_detail_util_pvt.get_request_context which has updated the primary_quantity
based on the quantity, quantity detailed calculations which is not reverted back
after creating suggestion. Reverting the primary quantity back to correct quantity
for inbound mtrls.. */
-- collecting values of current mtrl...
if (inv_cache.set_mol_rec(p_transaction_temp_id)) then
-- collecting values of current item + org combination..
if (inv_cache.set_item_rec(inv_cache.mol_rec.organization_id, inv_cache.mol_rec.inventory_item_id)) then
l_mtrl_pri_qty := inv_convert.inv_um_convert(
inv_cache.mol_rec.inventory_item_id,
5, -- precision
inv_cache.mol_rec.quantity,
inv_cache.mol_rec.uom_code,
inv_cache.item_rec.PRIMARY_UOM_CODE,
NULL,
NULL);
update mtl_txn_Request_lines mtrl
set primary_quantity = l_mtrl_pri_qty
where line_id = p_transaction_temp_id
and TRANSACTION_SOURCE_TYPE_ID in (1,7,12); -- only inbound mtrls