[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(sysdate,'MM/DD/YYYY HH:MM:SS') INTO l_ts from dual;
SELECT mtrl.line_id,
mtrl.inventory_item_id,
msi.item_type,
mtrl.project_id,
mtrl.task_id,
mtrl.uom_code,
muom.uom_class,
mtrl.last_update_login,
mtrl.reference,
mtrl.reference_id,
mtrl.transaction_source_type_id
FROM mtl_txn_request_lines mtrl,
mtl_system_items msi,
mtl_units_of_measure muom
WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
AND (mtrl.line_id = p_move_order_line_id OR p_move_order_line_id IS NULL)
AND mtrl.backorder_delivery_detail_id IS NULL -- this LPN has not crossdocked yet
AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
AND (inspection_status = 2 OR inspection_status IS NULL)
AND wms_process_flag = 1
AND msi.inventory_item_id = mtrl.inventory_item_id
AND msi.organization_id = mtrl.organization_id
AND mtrl.uom_code = muom.uom_code;
SELECT line_id,
inventory_item_id,
backorder_delivery_detail_id,
crossdock_type,
to_subinventory_code,
to_locator_id,
wip_supply_type,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id,
transaction_source_type_id
FROM
mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
AND mtrl.line_id = NVL(p_move_order_line_id, mtrl.line_id)
AND mtrl.backorder_delivery_detail_id IS NOT NULL -- also including lines planned crossdocked
AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
AND NVL(inspection_status, 2) = 2
AND wms_process_flag = 1
--BUG 5194761: If case of Item Load, p_move_order_line will be passed
--and we need to pick up the MOL that may be split by the crossdock API.
--So make use of the mtrl.reference_detail_id
UNION
SELECT line_id,
inventory_item_id,
backorder_delivery_detail_id,
crossdock_type,
to_subinventory_code,
to_locator_id,
wip_supply_type,
wip_entity_id,
operation_seq_num,
repetitive_schedule_id,
transaction_source_type_id
FROM
mtl_txn_request_lines mtrl
WHERE mtrl.lpn_id = l_lpn_id -- the MTRL is within the LPN
AND p_move_order_line_id IS NOT NULL
AND mtrl.reference_detail_id = p_move_order_line_id
AND mtrl.backorder_delivery_detail_id IS NOT NULL -- also including lines planned crossdocked
AND (mtrl.quantity_detailed = 0 OR mtrl.quantity_detailed IS NULL) -- no MMTT is created for this LPN
AND NVL(inspection_status, 2) = 2
AND wms_process_flag = 1;
SELECT Nvl(mp.crossdock_flag, 2),
mp.default_crossdock_criteria_id,
mp.default_crossdock_subinventory, -- default wip crossdocking sub
mp.default_crossdock_locator_id, -- default wip crossdocking loc
wsp.default_stage_subinventory,
wsp.default_stage_locator_id
INTO l_xdock_flag,
l_default_xdock_criteria_id,
l_default_xdock_sub, -- default wip crossdocking sub
l_default_xdock_loc_id,-- default wip crossdocking loc
l_default_ship_staging_sub,
l_default_ship_staging_loc_id
FROM mtl_parameters mp, wsh_shipping_parameters wsp
WHERE mp.organization_id = wsp.organization_id (+)
AND mp.organization_id = l_org_id;
SELECT location_id
INTO l_location_id
FROM rcv_supply
WHERE lpn_id = p_lpn
AND ROWNUM<2;
SELECT nvl(nvl(supply_subinventory, mp.default_crossdock_subinventory), wp.default_pull_supply_subinv),
nvl(nvl(supply_locator_id, mp.default_crossdock_locator_id), wp.default_pull_supply_locator_id)
INTO l_to_sub_code,
l_to_loc_id
FROM wip_requirement_operations wro,
mtl_txn_request_lines mtrl,
mtl_parameters mp,
wip_parameters wp
WHERE wro.organization_id = l_org_id
AND mp.organization_id = l_org_id
AND wp.organization_id = l_org_id
AND mtrl.line_id = l_line_id
AND wro.inventory_item_id = mtrl.inventory_item_id
AND wro.wip_entity_id = mtrl.wip_entity_id
AND nvl(wro.operation_seq_num, -1) = Nvl(mtrl.operation_seq_num, nvl(wro.operation_seq_num, -1))
AND nvl(wro.repetitive_schedule_id, -1) = Nvl(mtrl.repetitive_schedule_id, nvl(wro.repetitive_schedule_id, -1));
SELECT default_pull_supply_subinv,
default_pull_supply_locator_id
INTO l_to_sub_code,
l_to_loc_id
FROM wip_parameters
WHERE organization_id = l_org_id;
SELECT delivery_id
INTO l_delivery_id
FROM wsh_delivery_assignments_v
WHERE delivery_detail_id = l_backorder_delivery_detail_id;
SELECT 2
, inv_salesorder.get_salesorder_for_oeheader(wdd.source_header_id)
, wdd.source_line_id
INTO l_source_type_id, l_source_header_id, l_source_line_id
FROM wsh_delivery_details wdd
WHERE wdd.delivery_detail_id = l_backorder_delivery_detail_id;
SELECT crossdock_criteria_id,
demand_ship_date
INTO l_xdock_criterion_id,
l_expected_delivery_time
FROM mtl_reservations
WHERE demand_source_line_detail = l_backorder_delivery_detail_id
AND supply_source_type_id = inv_reservation_global.g_source_type_rcv
AND organization_id = l_org_id
AND inventory_item_id = l_inventory_item_id;
UPDATE mtl_txn_request_lines
SET to_subinventory_code = l_to_sub_code,
to_locator_id = l_to_loc_id
WHERE line_id = l_line_id;
UPDATE mtl_txn_request_lines
SET to_subinventory_code = Nvl(l_to_sub_code, l_default_ship_staging_sub),
to_locator_id = Nvl(l_to_loc_id, l_default_ship_staging_loc_id)
WHERE line_id = l_line_id;
l_update_rsv_rec INV_Reservation_GLOBAL.MTL_RESERVATION_REC_TYPE;
SELECT serial_number
FROM mtl_serial_numbers
WHERE lpn_id = l_lpn_id;
SELECT fm_serial_number
FROM mtl_serial_numbers_temp
WHERE transaction_temp_id=l_serial_temp_id ;
SELECT lpn_id, license_plate_number, parent_lpn_id, organization_id, subinventory_code, locator_id,
tare_weight, tare_weight_uom_code, gross_weight, gross_weight_uom_code,
container_volume, container_volume_uom, content_volume, content_volume_uom_code
FROM wms_license_plate_numbers
START WITH lpn_id = p_innermost_lpn_id
CONNECT BY lpn_id = PRIOR parent_lpn_id;
SELECT
l.line_id, l.backorder_delivery_detail_id,l.inventory_item_id,Nvl(crossdock_type,1)
,l.transaction_source_type_id,l.txn_source_id
INTO l_mo_line_id,l_del_id,l_item_id,l_crossdock_type,l_transaction_source_type_id,l_txn_supply_source_id
FROM mtl_txn_request_lines l, mtl_material_transactions_temp t, wsh_delivery_details_ob_grp_v wdd
WHERE t.transaction_temp_id=l_temp_id
AND l.backorder_delivery_detail_id = wdd.delivery_detail_id
AND t.move_order_line_id=l.line_id
AND exists (
select 1 from oe_order_lines_all oel
where oel.line_id = wdd.source_line_id
and nvl(oel.project_id,-9999) = nvl(l.project_id,-9999)
and nvl(oel.task_id,-9999) = nvl(l.task_id,-9999)
);
SELECT
l.line_id, l.backorder_delivery_detail_id,l.inventory_item_id,Nvl(crossdock_type,1)
,l.transaction_source_type_id,l.txn_source_id
INTO l_mo_line_id,l_del_id,l_item_id,l_crossdock_type,l_transaction_source_type_id,l_txn_supply_source_id
FROM mtl_txn_request_lines l, mtl_material_transactions_temp t
WHERE t.transaction_temp_id=l_temp_id
AND t.move_order_line_id=l.line_id;
SELECT t.primary_quantity,t.inventory_item_id,t.subinventory_code,
t.locator_id,t.revision,t.transaction_type_id,t.transfer_lpn_id,
t.content_lpn_id,i.primary_uom_code,i.lot_control_code,
i.serial_number_control_code
INTO l_prim_qty ,l_item_id,l_sub,
l_loc,l_rev,l_transaction_type_id,
l_transfer_lpn_id,l_cnt_lpn_id,l_prim_uom
,l_lot_control_code, l_serial_control_code
FROM mtl_material_transactions_temp t,
mtl_system_items i
WHERE t.transaction_temp_id=l_temp_id
AND t.organization_id=l_org_id
AND t.organization_id =i.organization_id
AND t.inventory_item_id=i.inventory_item_id;
SELECT lot_number,serial_transaction_temp_id INTO l_lot,
l_serial_temp_id
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id=l_temp_id;
SELECT * INTO l_demand_info
from wsh_inv_delivery_details_v
WHERE delivery_detail_id=l_del_id;
select Nvl(order_source_id,1)
into l_order_source_id
from oe_order_lines_all
where line_id = l_demand_info.oe_line_id;
SELECT Nvl(po_header_id, -1)
INTO l_query_rsv_rec.supply_source_header_id
FROM rcv_transactions
-- patchset j changes
WHERE transaction_id = l_txn_supply_source_id;
l_update_rsv_rec := l_reservation_tbl(i);
l_update_rsv_rec.demand_source_delivery := NULL;
l_update_rsv_rec.primary_uom_code := l_prim_uom;
l_update_rsv_rec.primary_uom_id := NULL;
l_update_rsv_rec.reservation_uom_code := NULL;
l_update_rsv_rec.reservation_uom_id := NULL;
l_update_rsv_rec.reservation_quantity := NULL;
l_update_rsv_rec.primary_reservation_quantity :=
l_reservation_tbl(i).primary_reservation_quantity;
l_update_rsv_rec.primary_reservation_quantity :=
l_primary_temp_qty;
l_update_rsv_rec.supply_source_type_id := INV_Reservation_GLOBAL.g_source_type_inv;
l_update_rsv_rec.supply_source_header_id := NULL;
l_update_rsv_rec.supply_source_line_id := NULL;
l_update_rsv_rec.supply_source_name := NULL;
l_update_rsv_rec.supply_source_line_detail := NULL;
l_update_rsv_rec.subinventory_code := l_sub;
l_update_rsv_rec.subinventory_id := NULL;
l_update_rsv_rec.locator_id := l_loc;
l_update_rsv_rec.lpn_id := l_transfer_lpn_id;
p_to_rsv_rec => l_update_rsv_rec,
p_original_serial_number => l_dummy_sn,
p_to_serial_number => l_dummy_sn,
p_validation_flag => fnd_api.g_true,
x_to_reservation_id => l_org_wide_res_id);
l_update_rsv_rec.reservation_id := NULL; -- cannot know
l_update_rsv_rec.requirement_date := Sysdate;
l_update_rsv_rec.organization_id := l_org_id;
l_update_rsv_rec.inventory_item_id := l_item_id;
l_update_rsv_rec.demand_source_type_id :=
INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INTERNAL_ORD; -- Internal Order
l_update_rsv_rec.demand_source_type_id :=
INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE; -- Order Entry
--l_update_rsv_rec.demand_source_type_id := inv_globals.G_SourceType_SalesOrder;
l_update_rsv_rec.demand_source_name := NULL;
l_update_rsv_rec.demand_source_header_id := l_mso_header_id;
l_update_rsv_rec.demand_source_line_id := l_demand_info.oe_line_id;
l_update_rsv_rec.demand_source_delivery := NULL;
l_update_rsv_rec.primary_uom_code := l_prim_uom;
l_update_rsv_rec.primary_uom_id := NULL;
l_update_rsv_rec.reservation_uom_code := NULL;
l_update_rsv_rec.reservation_uom_id := NULL;
l_update_rsv_rec.reservation_quantity := NULL;
l_update_rsv_rec.primary_reservation_quantity := l_prim_qty;
l_update_rsv_rec.autodetail_group_id := NULL;
l_update_rsv_rec.external_source_code := NULL;
l_update_rsv_rec.external_source_line_id := NULL;
l_update_rsv_rec.supply_source_type_id :=
INV_Reservation_GLOBAL.g_source_type_inv;
l_update_rsv_rec.supply_source_header_id := NULL;
l_update_rsv_rec.supply_source_line_id := NULL;
l_update_rsv_rec.supply_source_name := NULL;
l_update_rsv_rec.supply_source_line_detail := NULL;
l_update_rsv_rec.revision := l_rev;
l_update_rsv_rec.subinventory_code := l_sub;
l_update_rsv_rec.subinventory_id := NULL;
l_update_rsv_rec.locator_id := l_loc;
l_update_rsv_rec.lot_number := l_lot;
l_update_rsv_rec.lot_number_id := NULL;
l_update_rsv_rec.pick_slip_number := NULL;
l_update_rsv_rec.lpn_id := l_transfer_lpn_id;
l_update_rsv_rec.attribute_category := NULL;
l_update_rsv_rec.attribute1 := NULL;
l_update_rsv_rec.attribute2 := NULL;
l_update_rsv_rec.attribute3 := NULL;
l_update_rsv_rec.attribute4 := NULL;
l_update_rsv_rec.attribute5 := NULL;
l_update_rsv_rec.attribute6 := NULL;
l_update_rsv_rec.attribute7 := NULL;
l_update_rsv_rec.attribute8 := NULL;
l_update_rsv_rec.attribute9 := NULL;
l_update_rsv_rec.attribute10 := NULL;
l_update_rsv_rec.attribute11 := NULL;
l_update_rsv_rec.attribute12 := NULL;
l_update_rsv_rec.attribute13 := NULL;
l_update_rsv_rec.attribute14 := NULL;
l_update_rsv_rec.attribute15 := NULL;
l_update_rsv_rec.ship_ready_flag := NULL;
l_update_rsv_rec.detailed_quantity := 0;
, p_rsv_rec => l_update_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 => fnd_api.g_true
, x_quantity_reserved => l_qty_succ_reserved
, x_reservation_id => l_org_wide_res_id
);
-- staged. Am calling an API to update the reservation thusly..
IF (l_debug = 1) THEN
mydebug('Upd Reservation as having been staged');
mydebug('Calling API to update rsv as staged...');
inv_staged_reservation_util.update_staged_flag
( x_return_status =>l_return_status,
x_msg_count =>l_msg_cnt,
x_msg_data =>l_msg_data,
p_reservation_id =>l_org_wide_res_id,
p_staged_flag =>'Y');
mydebug('After calling API to update rsv as staged');
select oe_header_id, oe_line_id
into l_source_header_id, l_source_line_id
from wsh_inv_delivery_details_v
WHERE delivery_detail_id=l_del_id;
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
mydebug('after update shipping attributes');
UPDATE mtl_serial_numbers
SET group_mark_id = mtl_material_transactions_s.NEXTVAL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
mydebug('Number of serial number updated: ' || SQL%rowcount);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
mydebug('after update shipping attributes');
UPDATE mtl_serial_numbers
SET group_mark_id = mtl_material_transactions_s.NEXTVAL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
mydebug('Number of serial number updated: ' || SQL%rowcount);
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
UPDATE mtl_serial_numbers
SET group_mark_id = mtl_material_transactions_s.NEXTVAL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
mydebug('Number of serial number updated: ' || SQL%rowcount);
mydebug('after update shipping attributes');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
UPDATE mtl_serial_numbers
SET group_mark_id = mtl_material_transactions_s.NEXTVAL
WHERE serial_number = l_serial_number
AND inventory_item_id = l_item_id
AND current_organization_id = l_org_id;
mydebug('Number of serial number updated: ' || SQL%rowcount);
mydebug('after update shipping attributes');
mydebug('about to call update shipping attributes');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
mydebug('after update shipping attributes');
SELECT delivery_detail_id INTO l_lpn_del_detail_id
FROM wsh_delivery_details_ob_grp_v
WHERE lpn_id=l_transfer_lpn_id
AND ROWNUM=1;
mydebug('Update LPN context to picked');
SELECT 1 INTO l_ret
FROM wsh_delivery_details
WHERE lpn_id = parent_lpn_rec.lpn_id;
mydebug('Done with call to WSH Create_Update_Containers');
mydebug('Before calling update shipping');
WSH_INTERFACE.Update_Shipping_Attributes
(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status
);
SELECT status
INTO l_wdt_status
FROM wms_dispatched_tasks
WHERE transaction_temp_id = p_transaction_temp_id
AND task_type = wms_globals.g_wms_task_type_putaway;
UPDATE mtl_txn_request_lines
SET backorder_delivery_detail_id = NULL
, crossdock_type = 1
, quantity_detailed = (quantity - Nvl(quantity_delivered,0))
WHERE line_id = p_move_order_line_id;
INV_TRX_UTIL_PUB.Delete_transaction
(x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_transaction_temp_id => p_transaction_temp_id,
p_update_parent => FALSE);
SELECT mmtt.transaction_source_type_id
, mmtt.move_order_line_id
, mtrl.backorder_delivery_detail_id
INTO l_txn_src_type_id
, l_move_order_line_id
, l_backorder_delivery_detail_id
FROM mtl_material_transactions_temp mmtt
, mtl_txn_request_lines mtrl
, mtl_txn_request_headers mtrh
WHERE mmtt.transaction_temp_id = p_transaction_temp_id
AND mmtt.move_order_line_id = mtrl.line_id
AND mtrh.header_id = mtrl.header_id
AND mtrh.move_order_type = 6
AND mtrl.line_status = 7;