[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_rsv_rec inv_reservation_global.mtl_reservation_rec_type; -- Record for inserting reservations
SELECT header_id,
line_id,
inventory_item_id,
ship_from_org_id,
schedule_status_code,
'OE' source,
ordered_quantity qty -- added for debug only
FROM oe_order_lines_all ol,
mtl_parameters mtl
WHERE ol.ship_from_org_id = mtl.organization_id
AND mtl.process_enabled_flag = 'Y' AND
NVL(ol.shipping_interfaced_flag,'N')= 'N'
UNION ALL
SELECT wdd.source_header_id,
wdd.source_line_id,
wdd.inventory_item_id,
wdd.organization_id,
to_char(NULL) schedule_status_code,
'WDD' source,
0 qty
FROM wsh_delivery_details wdd,
mtl_parameters mtl
WHERE wdd.organization_id = mtl.organization_id
AND mtl.process_enabled_flag = 'Y'
AND wdd.released_status in ('B','R','S', 'X'); -- 5475003 need to include status X for non inv items
SELECT *
FROM ic_tran_pnd
WHERE doc_type = 'OMSO'
AND line_id = p_line_id
AND delete_mark = 0
AND staged_ind = 0
AND completed_ind = 0
AND abs(round(trans_qty,5)) > 0
AND (lot_id >0 OR location <> l_IC$DEFAULT_LOCT);
SELECT m.lot_divisible_flag,m.lot_control_code,m.tracking_quantity_ind, ic.NONINV_IND -- 5475003 rework
FROM mtl_system_items m, ic_item_mst ic -- 5475003 rework
WHERE inventory_item_id = p_item_id and organization_id = p_organization_id
AND m.segment1 = ic.item_no; -- 5475003 rework
SELECT subinventory_ind_flag, organization_id -- 5574631
FROM ic_whse_mst
WHERE whse_code = p_whse_code;
SELECT locator_id, inventory_location_id -- 5576431
FROM ic_loct_mst
WHERE whse_code = p_whse_code
AND location = p_location;
SELECT subinventory_code
FROM mtl_item_locations
WHERE inventory_location_id = p_inventory_location_id;
SELECT lot_no,sublot_no
FROM ic_lots_mst
WHERE item_id = p_item_id
AND lot_id = p_lot_id;
SELECT *
FROM ic_txn_request_lines
WHERE txn_source_line_id = p_line_id
AND line_status = 7; -- 7 is open , 5 is closed , 9 is cancelled
SELECT delivery_detail_id
FROM wsh_delivery_details
WHERE move_order_line_id = p_mo_line_id
AND source_line_id = p_line_id;
SELECT h.*
FROM ic_txn_request_headers h,
ic_txn_request_lines l
WHERE l.header_id = h.header_id
AND l.line_id = p_line_id;
SELECT schedule_status_code
FROM oe_order_lines_all
WHERE line_id = p_line_id;
select distinct wda.delivery_id
from wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda
where wdd.source_line_id = p_line_id
and wdd.delivery_detail_id = wda.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and wnd.delivery_id is not null;
SELECT default_stage_locator_id, pick_grouping_rule_id
FROM wsh_shipping_parameters
WHERE organization_id = p_org_id;
select sales_order_id
from oe_order_headers_all oe, mtl_sales_orders mtl
where oe.header_id = p_header_id and mtl.segment1 = oe.order_number;
l_insert_rsv_rec.reservation_id := NULL; -- cannot know
l_insert_rsv_rec.requirement_date := SYSDATE;
l_insert_rsv_rec.organization_id := open_order_line_rec.ship_from_org_id;
l_insert_rsv_rec.inventory_item_id := open_order_line_rec.inventory_item_id;
l_insert_rsv_rec.demand_source_type_id := 2; /* For the Sales Order line */
l_insert_rsv_rec.demand_source_name := NULL;
l_insert_rsv_rec.demand_source_header_id := allocations_for_line_rec.doc_id; --open_order_line_rec.header_id;
l_insert_rsv_rec.demand_source_line_id := open_order_line_rec.line_id;
l_insert_rsv_rec.demand_source_delivery := NULL;
l_insert_rsv_rec.primary_uom_code := l_primary_uom_code;
l_insert_rsv_rec.secondary_uom_code := l_secondary_uom_code;
l_insert_rsv_rec.primary_uom_id := NULL;
l_insert_rsv_rec.secondary_uom_id := NULL;
l_insert_rsv_rec.reservation_uom_code := NULL;
l_insert_rsv_rec.reservation_uom_id := NULL;
l_insert_rsv_rec.reservation_quantity := NULL;
l_insert_rsv_rec.primary_reservation_quantity := abs(trunc(allocations_for_line_rec.trans_qty,5)); -- 5616998
l_insert_rsv_rec.secondary_reservation_quantity:= abs(trunc(allocations_for_line_rec.trans_qty2,5)); -- 5616998
l_insert_rsv_rec.secondary_reservation_quantity := NULL; -- need to initialize this back else will fail
l_insert_rsv_rec.autodetail_group_id := NULL;
l_insert_rsv_rec.external_source_code := NULL;
l_insert_rsv_rec.external_source_line_id := NULL;
l_insert_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
l_insert_rsv_rec.supply_source_header_id := NULL;
l_insert_rsv_rec.supply_source_line_id := NULL;
l_insert_rsv_rec.supply_source_name := NULL;
l_insert_rsv_rec.supply_source_line_detail := NULL;
l_insert_rsv_rec.revision := NULL;
-- 1 update the SO line with new org (open_order_line_rec.ship_from_org_id) ;
-- 2 update any delivery details with new org;
-- 3 update wsh_new_deliveries (trips do NOT store orgs) with the new org (all because of GMD's requirements);
-- 5 update any mo lines with new org id -- 5731584
/* need to update the SO line with new org (open_order_line_rec.ship_from_org_id) */
GMI_RESERVATION_UTIL.PrintLn(' before update to ship_from_org_id of so line '|| open_order_line_rec.line_id);
UPDATE oe_order_lines_all
SET ship_from_org_id = l_organization_id
WHERE line_id = open_order_line_rec.line_id;
-- update any OPM mo lines with new org id so that new id will be used for migration down below -- 5731584
-- 5731584 start
UPDATE ic_txn_request_lines
SET organization_id = l_organization_id
WHERE txn_source_line_id = open_order_line_rec.line_id
AND line_status = 7;
/* possibly multiple delivery details are updated */
update wsh_delivery_details
set organization_id = l_organization_id
where source_line_id = open_order_line_rec.line_id;
GMI_RESERVATION_UTIL.PrintLn(' after update to wsh_delivery_details for organization_id '|| l_organization_id);
/* update the deliveries to reflect the new organization */
FOR wsh1 in c_wsh_deliveres (open_order_line_rec.line_id)
LOOP
GMI_RESERVATION_UTIL.PrintLn(' before update to wsh_delivery_details for organization_id '|| l_organization_id);
update wsh_new_deliveries
set organization_id = l_organization_id
where delivery_id = wsh1.delivery_id;
l_insert_rsv_rec.organization_id := l_organization_id;
GMI_RESERVATION_UTIL.PrintLn('primary_reservation_quantity ' || l_insert_rsv_rec.primary_reservation_quantity );
l_insert_rsv_rec.subinventory_id := NULL;
l_insert_rsv_rec.locator_id := l_locator_id;
l_insert_rsv_rec.subinventory_code := l_subinventory_code;
l_insert_rsv_rec.locator_id := NULL;
l_insert_rsv_rec.subinventory_code := NULL;
GMI_RESERVATION_UTIL.Println('locator_id is '|| l_insert_rsv_rec.locator_id);
l_insert_rsv_rec.lot_number := l_odm_lot_num;
l_insert_rsv_rec.lot_number_id := NULL;
l_insert_rsv_rec.pick_slip_number := NULL;
l_insert_rsv_rec.lpn_id := NULL;
l_insert_rsv_rec.attribute_category := NULL;
l_insert_rsv_rec.attribute1 := NULL;
l_insert_rsv_rec.attribute2 := NULL;
l_insert_rsv_rec.attribute3 := NULL;
l_insert_rsv_rec.attribute4 := NULL;
l_insert_rsv_rec.attribute5 := NULL;
l_insert_rsv_rec.attribute6 := NULL;
l_insert_rsv_rec.attribute7 := NULL;
l_insert_rsv_rec.attribute8 := NULL;
l_insert_rsv_rec.attribute9 := NULL;
l_insert_rsv_rec.attribute10 := NULL;
l_insert_rsv_rec.attribute11 := NULL;
l_insert_rsv_rec.attribute12 := NULL;
l_insert_rsv_rec.attribute13 := NULL;
l_insert_rsv_rec.attribute14 := NULL;
l_insert_rsv_rec.attribute15 := NULL;
l_insert_rsv_rec.ship_ready_flag := 2;
l_insert_rsv_rec.detailed_quantity := 0;
inv_reservation_pvt.print_rsv_rec(l_insert_rsv_rec);
, p_rsv_rec => l_insert_rsv_rec
, p_serial_number => l_dummy_sn
, x_serial_number => l_dummy_sn
, p_partial_reservation_flag => fnd_api.g_true
, p_force_reservation_flag => fnd_api.g_false
, p_validation_flag => 'Q'
, x_quantity_reserved => l_qty_reserved
, x_secondary_quantity_reserved=> l_sec_qty_reserved
, x_reservation_id => l_reservation_id
);
-- insert more comprehensive data into gma log table.
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'GMI_UNEXPECTED_ERROR',
p_table_name => 'IC_TRAN_PND',
p_context => 'CREATE RESERVATION',
p_param1 => INV_GMI_Migration.item(allocations_for_line_rec.item_id),
p_param2 => INV_GMI_Migration.lot(allocations_for_line_rec.lot_id),
p_param3 => allocations_for_line_rec.whse_code,
p_param4 => l_insert_rsv_rec.subinventory_code,
p_param5 => l_insert_rsv_rec.locator_id,
p_db_error => NULL,
p_app_short_name => 'GMI');
l_trohdr_rec.last_updated_by := FND_GLOBAL.user_id;
l_trohdr_rec.last_update_date := sysdate;
l_trohdr_rec.last_update_login := FND_GLOBAL.login_id;
-- insert more comprehensive data into gma log table.
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'INV_ERROR_CREATING_MO',
p_table_name => 'mtl_txn_request_headers',
p_context => 'Create_move_order_header',
p_param1 => l_ic_mo_header_rec.request_number,
p_param2 => l_ic_mo_header_rec.organization_id,
p_param3 => NULL,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => NULL,
p_app_short_name => 'INV');
/* Update attribute15 in ic_txn_request_headers */
GMI_RESERVATION_UTIL.PrintLn(' before update to attribute15 of in ic_txn_request_headers: '||to_char(l_trohdr_rec.header_id));
UPDATE ic_txn_request_headers
SET attribute15 = to_char(l_trohdr_rec.header_id)
WHERE header_id = l_ic_mo_header_rec.header_id;
GMI_RESERVATION_UTIL.PrintLn(' after update to attribute15 of in ic_txn_request_headers');
l_trolin_tbl(l_order_count).last_updated_by := FND_GLOBAL.USER_ID;
l_trolin_tbl(l_order_count).last_update_date := sysdate;
l_trolin_tbl(l_order_count).last_update_login := FND_GLOBAL.LOGIN_ID;
-- insert more comprehensive data into gma log table.
GMA_COMMON_LOGGING.gma_migration_central_log (
p_run_id => p_migration_run_id,
p_log_level => FND_LOG.LEVEL_ERROR,
p_message_token => 'INV_ERROR_CREATING_MO',
p_table_name => 'mtl_txn_request_lines',
p_context => 'Create_Move_Order_Lines',
p_param1 => l_mo_header_id,
p_param2 => move_order_line_rec.inventory_item_id,
p_param3 => open_order_line_rec.line_id,
p_param4 => NULL,
p_param5 => NULL,
p_db_error => NULL,
p_app_short_name => 'INV');
/* Update OPM move order line attribute15 */
l_mo_line_id := l_trolin_tbl(l_order_count).line_id;
GMI_RESERVATION_UTIL.PrintLn(' before update of ic_txn_request_lines to attribute15 to line: '||to_char(l_mo_line_id));
UPDATE ic_txn_request_lines
SET attribute15 = to_char(l_trolin_tbl(l_order_count).line_id)
WHERE line_id = move_order_line_rec.line_id;
GMI_RESERVATION_UTIL.PrintLn(' after update to ic_txn_request_lines to attribute15 to line: '||to_char(l_mo_line_id));
UPDATE mtl_txn_request_lines
SET to_locator_id = l_to_locator_id
WHERE line_id = l_line_id;
GMI_RESERVATION_UTIL.PrintLn(' locator_id updated to :- ' || l_to_locator_id);
/* UPDATE the WSH_DELIVERY_DETAILS with this new move order info */
GMI_RESERVATION_UTIL.PrintLn(' before update of wsh_delivery_detail for move order line id');
GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail delivery_detail_rec.delivery_detail_id '||to_char(delivery_detail_rec.delivery_detail_id));
GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail old ic_txn_request_lines line_id '||to_char(move_order_line_rec.line_id));
GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail open_order_line_rec.line_id '||to_char(open_order_line_rec.line_id));
GMI_RESERVATION_UTIL.PrintLn(' in loop to update wsh_delivery_detail l_opm_noninv_ind '||to_char(l_opm_noninv_ind ));
UPDATE wsh_delivery_details
SET released_status = 'Y', -- 5475003
picked_quantity = move_order_line_rec.quantity, -- 5475003
picked_quantity2 = move_order_line_rec.secondary_quantity -- 5475003
WHERE delivery_detail_id = delivery_detail_rec.delivery_detail_id;
UPDATE wsh_delivery_details
SET move_order_line_id = l_mo_line_id
WHERE delivery_detail_id = delivery_detail_rec.delivery_detail_id;
GMI_RESERVATION_UTIL.PrintLn(' in loop after update');