[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cc_entry_interface_id
from mtl_cc_entries_interface
where cycle_count_entry_id = p_cce_id and
(delete_flag <> 1 or delete_flag is null
OR not (status_flag in (0,1)));
update mtl_cc_entries_interface
set
lock_flag = 2
, last_update_date = sysdate
, last_updated_by = MTL_CCEOI_VAR_PVT.G_UserID
, last_update_login = MTL_CCEOI_VAR_PVT.G_LoginID
, request_id = MTL_CCEOI_VAR_PVT.G_RequestID
, program_application_id = MTL_CCEOI_VAR_PVT.G_ProgramAppID
, program_id = MTL_CCEOI_VAR_PVT.G_ProgramID
, program_update_date = sysdate
where
cc_entry_interface_id = p_iface_id;
select *
from mtl_cycle_count_entries
where cycle_count_entry_id = p_cce_id
for update of export_flag;
PROCEDURE Initial_Insert
(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2 ,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
x_iface_rec IN OUT NOCOPY MTL_CCEOI_VAR_PVT.INV_CCEOI_TYPE)
IS
-- Start OF comments
-- API name : Initial_Insert
-- TYPE : Private
-- Pre-reqs : None
-- Parameters:
-- IN : p_api_version IN NUMBER (required)
-- API Version of this procedure
-- p_init_msg_level IN VARCHAR2 (optional)
-- DEFAULT = FND_API.G_FALSE,
-- p_commit IN VARCHAR2 (optional)
-- DEFAULT = FND_API.G_FALSE,
-- p_validation_level IN NUMBER (optional)
-- DEFAULT = FND_API.G_VALID_LEVEL_FULL,
-- OUT : X_return_status OUT NUMBER
-- Result of all the operations
-- x_msg_count OUT NUMBER,
-- x_msg_data OUT VARCHAR2,
-- X_ErrorCode OUT NUMBER
-- RETURN value OF the Error status
-- IN OUT: x_iface_rec IN OUT CCEOI_Rec_Type (required)
-- complete interface RECORD
--
-- Version: Current Version 0.9
-- Initial version 0.9
-- Notes : Attempts to insert and lock passed to it p_iface_rec
-- into the mtl_cc_entries_interface table. If the interface record
-- corresponds to an existing count request in mtl_cycle_count_entries
-- then the count entry will be marked as exported unless it was already
-- exported. If count entry is already exported that would mean that it
-- is exported by a different interface record in which case interface
-- record is not inserted. If count entry os not exported then the SKU,
-- attribures, and account info will be derived from it, and that data
-- will copied into x_iface_rec and also inserted into a table
-- END OF comments
l_api_version number := 0.9;
l_api_name VARCHAR2(30) := 'Initial_Insert';
select *
from mtl_cc_entries_interface
where cc_entry_interface_id = ccei_id
for update of lock_flag;
SAVEPOINT Initial_Insert;
if (c_rec.delete_flag = 1) then
FND_MESSAGE.Set_Name('INV', 'INV_CCEOI_IFACE_MARKED_DELETED');
mtl_cceoi_process_pvt.Update_CCIEntry(x_iface_rec,x_return_status);
-- inserted into the interface (errors > 200)
if (x_errorcode < 0) then
raise fnd_api.g_exc_unexpected_error;
mtl_cceoi_process_pvt.Update_CCIEntry(x_iface_rec,x_return_status);
SELECT MTL_CC_ENTRIES_INTERFACE_S1.nextval
INTO x_iface_rec.cc_entry_interface_id
FROM dual;
-- inserted into the interface (errors > 200)
-- otherwise just insert record in the interface and let
-- errors show up during processing
if (x_errorcode < 0) then
raise fnd_api.g_exc_unexpected_error;
mtl_cceoi_process_pvt.Insert_CCIEntry(x_iface_rec, x_return_status);
ROLLBACK TO Initial_Insert;
ROLLBACK TO Initial_Insert;
ROLLBACK TO Initial_Insert;
Initial_Insert(
p_api_version => 0.9,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_iface_rec => L_interface_rec );
Initial_Insert(
p_api_version => 0.9,
x_return_status => x_return_status,
x_errorcode => x_errorcode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_iface_rec => L_interface_rec );
mdebug('initial_insert errocode='||x_errorcode);
-- insert record into the interface table if necessary and
-- set an error
FND_MESSAGE.SET_NAME('INV', 'INV_CCEOI_UNKNOWN_ACTION_CODE');
-- insert will raise an exception
MTL_CCEOI_PROCESS_PVT.Insert_CCIEntry(
p_interface_rec => p_interface_rec,
x_return_status => x_return_status);
MTL_CCEOI_PROCESS_PVT.Insert_CCEOIError(
p_cc_entry_interface_id =>
MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
p_error_column_name => 'ACTION_CODE'
, p_error_table_name => 'MTL_CC_ENTRIES_INTERFACE_ID'
, p_message_name => 'INV_CCEOI_UNKNOWN_ACTION_CODE');
-- it will be deleted by purge if status_flag in (0,1)
/* MTL_CCEOI_PROCESS_PVT.Set_CCEOIFlags(
p_api_version => 0.9
, p_init_msg_list => FND_API.G_TRUE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_cc_entry_interface_id => MTL_CCEOI_VAR_PVT.G_CC_ENTRY_INTERFACE_ID,
--=>L_interface_rec.cc_entry_interface_id,
p_flags => '$1$$');
update mtl_cc_entries_interface
set inventory_item_id = MTL_CCEOI_VAR_PVT.G_INVENTORY_ITEM_ID,
locator_id = MTL_CCEOI_VAR_PVT.G_LOCATOR_ID
where cc_entry_interface_id = mtl_cceoi_var_pvt.g_cc_entry_interface_id
and inventory_item_id is null;
SELECT subinventory_code, locator_id
INTO l_sub, l_loc_id
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = l_lpn.lpn_id
AND organization_id = p_interface_rec.organization_id;
SELECT container_enabled_flag, container_adjustment_option, container_discrepancy_option
INTO MTL_CCEOI_VAR_PVT.G_CONTAINER_ENABLED_FLAG, MTL_CCEOI_VAR_PVT.G_CONTAINER_ADJUSTMENT_OPTION,
MTL_CCEOI_VAR_PVT.G_CONTAINER_DISCREPANCY_OPTION
FROM MTL_CYCLE_COUNT_HEADERS
WHERE cycle_count_header_id = p_interface_rec.cycle_count_header_id;
SELECT lpn_context
INTO l_lpn_context
FROM wms_license_plate_numbers
WHERE lpn_id = p_interface_rec.parent_lpn_id;
SELECT unscheduled_count_entry
INTO l_unsched_allowed
FROM mtl_cycle_count_headers
WHERE cycle_count_header_id = p_interface_rec.cycle_count_header_id;
/* SELECT cost_group_id
INTO l_expl_tbl(v_index).cost_group_id
FROM mtl_cycle_count_entries
WHERE organization_id = l_expl_tbl(v_index).organization_id
AND inventory_item_id = l_expl_tbl(v_index).content_item_id
AND parent_lpn_id = l_expl_tbl(v_index).parent_lpn_id
AND cycle_count_header_id = p_interface_rec.cycle_count_header_id;*/
SELECT count_list_sequence
INTO l_item_rec.count_list_sequence
FROM mtl_cycle_count_entries
WHERE organization_id = l_expl_tbl(v_index).organization_id
AND inventory_item_id = l_expl_tbl(v_index).content_item_id
AND parent_lpn_id = l_expl_tbl(v_index).parent_lpn_id
AND NVL(cost_group_id, -1) = NVL(l_expl_tbl(v_index).cost_group_id, -1)
AND cycle_count_header_id = p_interface_rec.cycle_count_header_id
AND NVL(lot_number, -1) = NVL(l_expl_tbl(v_index).lot_number, -1)
AND NVL(revision, -1) = NVL(l_expl_tbl(v_index).revision, -1) -- Bug : 7504490
AND Nvl(serial_number,-1) = NVL(l_expl_tbl(v_index).serial_number, -1) -- 8712932 added for serial number
AND entry_status_code IN (1,3);
-- insert interface id into table
x_interface_id_list(l_counter) := l_temp_int_id;
SELECT mtl_material_transactions_s.nextval
INTO L_txn_header_id
FROM dual;