[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* GME_NO_MATERIALS_SELECTED */
-- HALUTHRA 22-SEP-2008 Bug 7383625
-- Added IF condition to populate detailed_quantity
-- in PROCEDURE process_line
-- A.Mishra 15-May-2009 Bug 8481421
-- The fix includes adding the code to also update the Quantity Tree along with
-- the Material reservation, which was missing initially.
-- Procedure Process_line is modified to add the code for updating the quantity tree by calling
-- the code from the INV side (inv_quantity_tree_pub.update_quantities)
-- G.Muratore 31-Aug-2010 Bug 9941121
-- Pass grouping rule id to create_move_order_hdr procedure.
-- Procedure: pick_material
-- G.Muratore 22-Jun-2011 Bug 12613813
-- Picking is now also considering the locator value.
-- Procedure: process_line
-- G.Muratore 02-DEC-2011 Bug 13076579
-- Code is restructured to only create move order header and lines for picking if the
-- open qty is more than the sum of non detailed qty across all open move order lines.
-- PROCEDURE: pick_material
/*************************************************************************************************/
PROCEDURE conc_picking (
err_buf OUT NOCOPY VARCHAR2
,ret_code OUT NOCOPY VARCHAR2
,p_organization_id IN NUMBER
,p_all_batches IN VARCHAR2
, -- 1 = All, 2 = Backordered
p_include_pending IN VARCHAR2
,p_include_wip IN VARCHAR2
,p_from_batch IN VARCHAR2
,p_to_batch IN VARCHAR2
,p_oprn_no IN VARCHAR2
,p_oprn_vers IN NUMBER
,p_product_no IN VARCHAR2
,p_ingredient_no IN VARCHAR2
,p_days_forward IN NUMBER
,p_from_req_date IN VARCHAR2
,p_to_req_date IN VARCHAR2
,p_pick_grouping_rule IN VARCHAR2
,p_print_pick_slip IN VARCHAR2 DEFAULT 'N'
,p_plan_tasks IN VARCHAR2 DEFAULT 'N'
,p_sales_order IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'conc_picking';
|| ' AND (:product_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
|| ' WHERE organization_id = :organization_id'
|| ' AND line_type = 1 AND inventory_item_id IN'
|| ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
|| ' AND concatenated_segments LIKE :product_no)))'
|| ' AND (:ingredient_no IS NULL OR batch_id IN (SELECT DISTINCT batch_id FROM gme_material_details'
|| ' WHERE organization_id = :organization_id'
|| ' AND line_type = -1 AND inventory_item_id IN'
|| ' (SELECT inventory_item_id FROM mtl_system_items_kfv WHERE organization_id = :organization_id'
|| ' AND concatenated_segments LIKE :ingredient_no)))'
|| ' AND material_requirement_date <= SYSDATE + NVL(:days_forward, 100000)'
|| ' AND material_requirement_date >= NVL(:from_req_date, material_requirement_date)'
|| ' AND material_requirement_date <= NVL(:to_req_date, material_requirement_date)';
l_sql_stmt := 'SELECT * FROM gme_ingred_pick_vw WHERE ' || l_where;
SELECT l.line_id, l.header_id
FROM mtl_txn_request_lines l, mtl_txn_request_headers h
WHERE l.organization_id = v_org_id
AND transaction_source_type_id =
gme_common_pvt.g_txn_source_type
AND l.txn_source_id = v_batch_id
AND l.txn_source_line_id = v_material_detail_id
-- Bug 13076579 - exclude those that are already fully detailed.
AND l.quantity <> l.quantity_detailed
AND l.line_status NOT IN (5, 6)
AND h.header_id = l.header_id
AND h.move_order_type NOT IN
(gme_common_pvt.g_invis_move_order_type
,inv_globals.g_move_order_put_away)
ORDER BY l.header_id, l.line_id;
SELECT count(1)
FROM mtl_txn_request_lines l, mtl_txn_request_headers h
WHERE l.organization_id = v_org_id
AND transaction_source_type_id =
gme_common_pvt.g_txn_source_type
AND l.txn_source_id = v_batch_id
AND l.txn_source_line_id = v_material_detail_id
AND l.line_status NOT IN (5, 6)
AND h.header_id = l.header_id
AND h.move_order_type NOT IN
(gme_common_pvt.g_invis_move_order_type
,inv_globals.g_move_order_put_away);
SELECT NVL(sum(l.quantity - l.quantity_detailed), 0)
FROM mtl_txn_request_lines l, mtl_txn_request_headers h
WHERE l.organization_id = v_org_id
AND transaction_source_type_id =
gme_common_pvt.g_txn_source_type
AND l.txn_source_id = v_batch_id
AND l.txn_source_line_id = v_material_detail_id
AND l.line_status NOT IN (5, 6)
AND h.header_id = l.header_id
AND h.move_order_type NOT IN
(gme_common_pvt.g_invis_move_order_type
,inv_globals.g_move_order_put_away)
GROUP BY l.txn_source_line_id;
FND_MESSAGE.SET_NAME('GME','GME_NO_MATERIALS_SELECTED');
SELECT SUM (transaction_quantity) qty_detailed
,SUM (secondary_transaction_quantity) sec_qty_detailed
FROM mtl_material_transactions_temp
WHERE move_order_line_id = v_move_order_line_id;
SELECT *
FROM mtl_material_transactions_temp
WHERE move_order_line_id = v_move_order_line_id;
SELECT *
INTO item_rec
FROM MTL_SYSTEM_ITEMS
WHERE organization_id = l_inv_resv_tbl(l_res_ordered_index).organization_id
AND inventory_item_id = l_inv_resv_tbl(l_res_ordered_index).inventory_item_id;
SELECT NVL(SUM(ABS(primary_quantity)), 0)
, NVL(SUM(ABS(secondary_transaction_quantity)), 0)
INTO l_reservation_detailed_qty
, l_rsv_detailed_qty2
FROM mtl_material_transactions_temp
WHERE organization_id = p_mo_line_rec.organization_id
AND reservation_id = l_reservation_id;
inv_quantity_tree_pub.update_quantities(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_api_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_organization_id => p_mo_line_rec.organization_id
, p_inventory_item_id => p_mo_line_rec.inventory_item_id
, p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
, p_is_revision_control => l_is_revision_control
, p_is_lot_control => l_is_lot_control
, p_is_serial_control => l_is_serial_control
, p_demand_source_type_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_type_id
, p_demand_source_header_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_header_id
, p_demand_source_line_id => l_inv_resv_tbl(l_res_ordered_index).demand_source_line_id
, p_demand_source_name => NULL
, p_revision => l_inv_resv_tbl(l_res_ordered_index).revision
, p_lot_number => l_inv_resv_tbl(l_res_ordered_index).lot_number
, p_lot_expiration_date => SYSDATE
, p_subinventory_code => l_inv_resv_tbl(l_res_ordered_index).subinventory_code
, p_locator_id => l_inv_resv_tbl(l_res_ordered_index).locator_id
, p_primary_quantity => -(l_reservation_detailed_qty - l_prev_rsv_detailed_qty)
, p_secondary_quantity => -(l_rsv_detailed_qty2 - l_prev_rsv_detailed_qty2)
, p_lpn_id => l_inv_resv_tbl(l_res_ordered_index).lpn_id
, p_quantity_type => inv_quantity_tree_pub.g_qr_same_demand
, x_qoh => l_qty_on_hand
, x_rqoh => l_qty_res_on_hand
, x_qr => l_qty_res
, x_qs => l_qty_sug
, x_att => l_qty_att
, x_atr => l_qty_available_to_reserve
, p_grade_code => p_mo_line_rec.grade_code
, x_sqoh => l_sec_qty_on_hand
, x_srqoh => l_sec_qty_res_on_hand
, x_sqr => l_sec_qty_res
, x_sqs => l_sec_qty_sug
, x_satt => l_sec_qty_att
, x_satr => l_sec_qty_available_to_reserve
);
UPDATE mtl_reservations
SET detailed_quantity = l_reservation_detailed_qty
,secondary_detailed_quantity = l_rsv_detailed_qty2
WHERE reservation_id = l_reservation_id;
UPDATE mtl_material_transactions_temp
SET pick_slip_number = l_pick_slip_number,
wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
UPDATE mtl_material_transactions_temp
SET wip_entity_type = gme_common_pvt.g_wip_entity_type_batch
WHERE transaction_temp_id = get_mmtt.transaction_temp_id;
inv_trolin_util.update_row (l_mo_line_rec);
UPDATE gme_material_details
SET backordered_qty = l_backordered_qty
WHERE material_detail_id = p_mo_line_rec.txn_source_line_id;