[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
from mtl_reservations
where demand_source_type_id =p_demand_source_type_id
and demand_source_line_id = p_demand_source_line_id
and supply_source_type_id = 13
and nvl(Staged_flag,'N') <> 'Y'
and nvl(detailed_quantity,0) = 0
order by primary_reservation_quantity;
l_rsv_rec_tbl.delete;
print_debug('Update shipping that ship set detailing failed',
'Inv_Express_Pick_Pub.Pick_Release');
and also mark serials and update reservations.
This require Digital enhancement as pre-req to work correctly if items are serial controlled
since we are not exploding serialized items.
Kalyan
*/
TYPE inv_staged_rsv_id_rec IS RECORD
( delivery_detail_id NUMBER
,Split_delivery_detail_id NUMBER
,Reservation_id NUMBER
,Transaction_temp_id NUMBER
,l_serial_index NUMBER
,staged_quantity NUMBER
,staged_secondary_quantity NUMBER --INVCONV kkillams
);
inv_staged_reservation_util.update_staged_flag(x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_reservation_id => l_reservation_id,
p_staged_flag => 'Y');
SELECT delivery_detail_id
, source_header_id
, source_line_id
, preferred_grade --INVCONV kkillams
INTO l_delivery_detail_id
, l_source_header_id
, l_source_line_id
, l_preferred_grade --INVCONV kkillams
FROM wsh_delivery_details
WHERE delivery_detail_id =
nvl(l_staged_rsv_id_tbl(l_res_index).split_delivery_detail_id,
l_staged_rsv_id_tbl(l_res_index).delivery_detail_id)
FOR UPDATE NOWAIT;
SELECT fm_serial_number INTO l_serial_number
FROM mtl_serial_numbers_temp WHERE
transaction_temp_id = l_staged_rsv_id_tbl(l_res_index).transaction_temp_id;
wsh_interface.update_shipping_attributes(p_source_code => 'INV',
p_changed_attributes => l_shipping_attr,
x_return_status => l_return_status);
SELECT msn.serial_number
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_organization_id
AND nvl(msn.revision,'@@@') = nvl(p_revision,'@@@')
AND nvl(msn.lot_number, '@@@') = nvl(p_lot_number,'@@@')
AND nvl(msn.current_subinventory_code,'@@@') = nvl(p_subinventory_code,'@@@')
AND nvl(msn.current_locator_id,-1) = nvl(p_locator_id,-1)
-- AND nvl(msn.end_item_unit_number,'@@@') = nvl(p_unit_number,'@@@')
AND msn.current_status = 3
AND ((msn.group_mark_id is null) or (msn.group_mark_id = -1))
ORDER BY msn.serial_number;
SELECT msn.serial_number
FROM mtl_serial_numbers msn
WHERE msn.reservation_id = p_reservation_id
ORDER BY msn.serial_number;
SELECT mtl_material_transactions_s.NEXTVAL into g_transaction_temp_id
FROM dual;
SELECT serial_number into l_serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = msn_rec.serial_number
FOR UPDATE nowait;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,vendor_serial_number
,vendor_lot_number
,fm_serial_number
,to_serial_number
,serial_prefix
,error_code
,group_header_id
,parent_serial_number
,end_item_unit_number
)
VALUES
(
g_transaction_temp_id
,sysdate
,g_user_id
,sysdate
,g_user_id
,null
,g_request_id
,g_prog_appid
,G_PROG_ID
,sysdate
,null
,null
,msn_rec.serial_number
,msn_rec.serial_number
,1
,null
,null
,null
,null
);
UPDATE mtl_serial_numbers
SET group_mark_id = g_transaction_temp_id
WHERE inventory_item_id = p_inventory_item_id
AND serial_number =msn_rec.serial_number;
SELECT mtl_material_transactions_s.NEXTVAL into g_transaction_temp_id
FROM dual;
SELECT serial_number into l_serial_number
FROM mtl_serial_numbers
WHERE inventory_item_id = p_inventory_item_id
AND serial_number = msn_rec.serial_number
FOR UPDATE nowait;
INSERT INTO mtl_serial_numbers_temp
(
transaction_temp_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,vendor_serial_number
,vendor_lot_number
,fm_serial_number
,to_serial_number
,serial_prefix
,error_code
,group_header_id
,parent_serial_number
,end_item_unit_number
)
VALUES
(
g_transaction_temp_id
,sysdate
,g_user_id
,sysdate
,g_user_id
,null
,g_request_id
,g_prog_appid
,G_PROG_ID
,sysdate
,null
,null
,msn_rec.serial_number
,msn_rec.serial_number
,1
,null
,null
,null
,null
);
UPDATE mtl_serial_numbers
SET group_mark_id = g_transaction_temp_id,
reservation_id = p_reservation_id
WHERE inventory_item_id = p_inventory_item_id
AND serial_number =msn_rec.serial_number;