[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_serial_attr ( p_calling_mode IN NUMBER,
p_serial_number_rec IN WSM_Serial_Support_GRP.WSM_SERIAL_NUM_REC,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_clear_serial_attr IN NUMBER DEFAULT NULL, -- will be used in case of WLT SpUA to clear the attributes
p_wlt_txn_type IN NUMBER DEFAULT NULL,
-- Pass the serial attribute context corresponding to the inventory item id
p_serial_attr_context IN VARCHAR2 DEFAULT NULL,
p_update_serial_attr IN NUMBER,
p_update_desc_attr IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
);
select
wsti.Serial_Number ,
null , -- assembly_item_id
wsti.header_id , -- header_id
wsti.Generate_serial_number ,
wsti.Generate_for_qty ,
wsti.Action_flag ,
wsti.Current_wip_entity_name ,
wsti.Changed_wip_entity_name ,
wsti.Current_wip_entity_id ,
wsti.Changed_wip_entity_id ,
decode(wsti.serial_attribute_category , l_null_char, null, null, msn.serial_attribute_category, wsti.serial_attribute_category), -- serial_attribute_category
decode(wsti.territory_code , l_null_char, null, null, msn.territory_code , wsti.territory_code ), -- territory_code
decode(wsti.origination_date , l_null_date, null, null, msn.origination_date , wsti.origination_date ), -- origination_date
decode(wsti.c_attribute1 , l_null_char, null, null, msn.c_attribute1 , wsti.c_attribute1 ), -- c_attribute1
decode(wsti.c_attribute2 , l_null_char, null, null, msn.c_attribute2 , wsti.c_attribute2 ), -- c_attribute2
decode(wsti.c_attribute3 , l_null_char, null, null, msn.c_attribute3 , wsti.c_attribute3 ), -- c_attribute3
decode(wsti.c_attribute4 , l_null_char, null, null, msn.c_attribute4 , wsti.c_attribute4 ), -- c_attribute4
decode(wsti.c_attribute5 , l_null_char, null, null, msn.c_attribute5 , wsti.c_attribute5 ), -- c_attribute5
decode(wsti.c_attribute6 , l_null_char, null, null, msn.c_attribute6 , wsti.c_attribute6 ), -- c_attribute6
decode(wsti.c_attribute7 , l_null_char, null, null, msn.c_attribute7 , wsti.c_attribute7 ), -- c_attribute7
decode(wsti.c_attribute8 , l_null_char, null, null, msn.c_attribute8 , wsti.c_attribute8 ), -- c_attribute8
decode(wsti.c_attribute9 , l_null_char, null, null, msn.c_attribute9 , wsti.c_attribute9 ), -- c_attribute9
decode(wsti.c_attribute10 , l_null_char, null, null, msn.c_attribute10 , wsti.c_attribute10 ), -- c_attribute10
decode(wsti.c_attribute11 , l_null_char, null, null, msn.c_attribute11 , wsti.c_attribute11 ), -- c_attribute11
decode(wsti.c_attribute12 , l_null_char, null, null, msn.c_attribute12 , wsti.c_attribute12 ), -- c_attribute12
decode(wsti.c_attribute13 , l_null_char, null, null, msn.c_attribute13 , wsti.c_attribute13 ), -- c_attribute13
decode(wsti.c_attribute14 , l_null_char, null, null, msn.c_attribute14 , wsti.c_attribute14 ), -- c_attribute14
decode(wsti.c_attribute15 , l_null_char, null, null, msn.c_attribute15 , wsti.c_attribute15 ), -- c_attribute15
decode(wsti.c_attribute16 , l_null_char, null, null, msn.c_attribute16 , wsti.c_attribute16 ), -- c_attribute16
decode(wsti.c_attribute17 , l_null_char, null, null, msn.c_attribute17 , wsti.c_attribute17 ), -- c_attribute17
decode(wsti.c_attribute18 , l_null_char, null, null, msn.c_attribute18 , wsti.c_attribute18 ), -- c_attribute18
decode(wsti.c_attribute19 , l_null_char, null, null, msn.c_attribute19 , wsti.c_attribute19 ), -- c_attribute19
decode(wsti.c_attribute20 , l_null_char, null, null, msn.c_attribute20 , wsti.c_attribute20 ), -- c_attribute20
decode(wsti.d_attribute1 , l_null_date, null, null, msn.d_attribute1 , wsti.d_attribute1 ), -- d_attribute1
decode(wsti.d_attribute2 , l_null_date, null, null, msn.d_attribute2 , wsti.d_attribute2 ), -- d_attribute2
decode(wsti.d_attribute3 , l_null_date, null, null, msn.d_attribute3 , wsti.d_attribute3 ), -- d_attribute3
decode(wsti.d_attribute4 , l_null_date, null, null, msn.d_attribute4 , wsti.d_attribute4 ), -- d_attribute4
decode(wsti.d_attribute5 , l_null_date, null, null, msn.d_attribute5 , wsti.d_attribute5 ), -- d_attribute5
decode(wsti.d_attribute6 , l_null_date, null, null, msn.d_attribute6 , wsti.d_attribute6 ), -- d_attribute6
decode(wsti.d_attribute7 , l_null_date, null, null, msn.d_attribute7 , wsti.d_attribute7 ), -- d_attribute7
decode(wsti.d_attribute8 , l_null_date, null, null, msn.d_attribute8 , wsti.d_attribute8 ), -- d_attribute8
decode(wsti.d_attribute9 , l_null_date, null, null, msn.d_attribute9 , wsti.d_attribute9 ), -- d_attribute9
decode(wsti.d_attribute10 , l_null_date, null, null, msn.d_attribute10 , wsti.d_attribute10 ), -- d_attribute10
decode(wsti.n_attribute1 , l_null_num , null, null, msn.n_attribute1 , wsti.n_attribute1 ), -- n_attribute1
decode(wsti.n_attribute2 , l_null_num , null, null, msn.n_attribute2 , wsti.n_attribute2 ), -- n_attribute2
decode(wsti.n_attribute3 , l_null_num , null, null, msn.n_attribute3 , wsti.n_attribute3 ), -- n_attribute3
decode(wsti.n_attribute4 , l_null_num , null, null, msn.n_attribute4 , wsti.n_attribute4 ), -- n_attribute4
decode(wsti.n_attribute5 , l_null_num , null, null, msn.n_attribute5 , wsti.n_attribute5 ), -- n_attribute5
decode(wsti.n_attribute6 , l_null_num , null, null, msn.n_attribute6 , wsti.n_attribute6 ), -- n_attribute6
decode(wsti.n_attribute7 , l_null_num , null, null, msn.n_attribute7 , wsti.n_attribute7 ), -- n_attribute7
decode(wsti.n_attribute8 , l_null_num , null, null, msn.n_attribute8 , wsti.n_attribute8 ), -- n_attribute8
decode(wsti.n_attribute9 , l_null_num , null, null, msn.n_attribute9 , wsti.n_attribute9 ), -- n_attribute9
decode(wsti.n_attribute10 , l_null_num , null, null, msn.n_attribute10 , wsti.n_attribute10 ), -- n_attribute10
decode(wsti.status_id , l_null_num , null, null, msn.status_id , wsti.status_id ), -- status_id
decode(wsti.time_since_new , l_null_num , null, null, msn.time_since_new , wsti.time_since_new ), -- time_since_new
decode(wsti.cycles_since_new , l_null_num , null, null, msn.cycles_since_new , wsti.cycles_since_new ), -- cycles_since_new
decode(wsti.time_since_overhaul , l_null_num , null, null, msn.time_since_overhaul , wsti.time_since_overhaul ), -- time_since_overhaul
decode(wsti.cycles_since_overhaul , l_null_num , null, null, msn.cycles_since_overhaul , wsti.cycles_since_overhaul ), -- cycles_since_overhaul
decode(wsti.time_since_repair , l_null_num , null, null, msn.time_since_repair , wsti.time_since_repair ), -- time_since_repair
decode(wsti.cycles_since_repair , l_null_num , null, null, msn.cycles_since_repair , wsti.cycles_since_repair ), -- cycles_since_repair
decode(wsti.time_since_visit , l_null_num , null, null, msn.time_since_visit , wsti.time_since_visit ), -- time_since_visit
decode(wsti.cycles_since_visit , l_null_num , null, null, msn.cycles_since_visit , wsti.cycles_since_visit ), -- cycles_since_visit
decode(wsti.time_since_mark , l_null_num , null, null, msn.time_since_mark , wsti.time_since_mark ), -- time_since_mark
decode(wsti.cycles_since_mark , l_null_num , null, null, msn.cycles_since_mark , wsti.cycles_since_mark ), -- cycles_since_mark
decode(wsti.number_of_repairs , l_null_num , null, null, msn.number_of_repairs , wsti.number_of_repairs ), -- number_of_repairs
decode(wsti.attribute_category , l_null_char, l_miss_char , null ,msn.attribute_category ,wsti.attribute_category ),
decode(wsti.attribute1 , l_null_char ,l_miss_char , wsti.attribute1 ),
decode(wsti.attribute2 , l_null_char ,l_miss_char , wsti.attribute2 ),
decode(wsti.attribute3 , l_null_char ,l_miss_char , wsti.attribute3 ),
decode(wsti.attribute4 , l_null_char ,l_miss_char , wsti.attribute4 ),
decode(wsti.attribute5 , l_null_char ,l_miss_char , wsti.attribute5 ),
decode(wsti.attribute6 , l_null_char ,l_miss_char , wsti.attribute6 ),
decode(wsti.attribute7 , l_null_char ,l_miss_char , wsti.attribute7 ),
decode(wsti.attribute8 , l_null_char ,l_miss_char , wsti.attribute8 ),
decode(wsti.attribute9 , l_null_char ,l_miss_char , wsti.attribute9 ),
decode(wsti.attribute10 , l_null_char ,l_miss_char , wsti.attribute10 ),
decode(wsti.attribute11 , l_null_char ,l_miss_char , wsti.attribute11 ),
decode(wsti.attribute12 , l_null_char ,l_miss_char , wsti.attribute12 ),
decode(wsti.attribute13 , l_null_char ,l_miss_char , wsti.attribute13 ),
decode(wsti.attribute14 , l_null_char ,l_miss_char , wsti.attribute14 ),
decode(wsti.attribute15 , l_null_char ,l_miss_char , wsti.attribute15 )
from wsm_serial_txn_interface wsti,
mtl_serial_numbers msn
where header_id = p_header_id
and transaction_type_id = 1
and wsti.serial_number = msn.serial_number (+)
and msn.inventory_item_id (+) = p_inventory_item_id
and msn.current_organization_id (+) = p_organization_id
order by nvl(wsti.action_flag,0) desc; -- Code review remark
l_param_tbl.delete;
select status_type
into l_status_type
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT nvl(SERIAL_NUMBER_CONTROL_CODE,1)
INTO x_serial_ctrl_code
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.inventory_item_id = p_serial_item_id
AND MSI.organization_id = p_organization_id;
l_msg_tokens.delete;
SELECT WDJ.serialization_start_op,
first_serial_txn_id,
WLBJ.serialization_start_op
INTO x_serial_start_flag,
x_first_serial_txn_id,
x_serial_start_op
FROM WSM_LOT_BASED_JOBS WLBJ,WIP_DISCRETE_JOBS WDJ
WHERE WLBJ.wip_entity_id = p_wip_entity_id
AND WDJ.wip_entity_id = WLBJ.wip_entity_id
AND WLBJ.organization_id = p_organization_id;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
SELECT start_quantity
into l_job_qty
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
SELECT max(operation_seq_num)
INTO l_op_seq_num
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND ((quantity_in_queue <> 0
OR quantity_running <> 0
OR quantity_waiting_to_move <> 0)
OR (quantity_in_queue = 0
and quantity_running = 0
and quantity_waiting_to_move = 0
and quantity_scrapped = quantity_completed
and quantity_completed > 0));
SELECT quantity_in_queue,
quantity_running,
quantity_waiting_to_move
INTO l_qty_queue,
l_qty_run,
l_qty_tomove
FROM wip_operations
WHERE wip_entity_id = p_wip_entity_id
AND operation_seq_num = l_op_seq_num;
SELECT count(*)
into l_serial_num_count
-- ST : Fix for bug 4910758 (remove usage of wsm_job_serial_numbers_v)
-- from wsm_job_serial_numbers_v
from mtl_serial_numbers
where wip_entity_id = p_wip_entity_id
and inventory_item_id = p_item_id
and current_organization_id = p_organization_id;
(p_wsm_serial_nums_tbl(l_index).action_flag <> WSM_UPDATE_SERIAL_NUM)
then -- Serialization has begun and action is not update...
l_stmt_num := 100;
l_msg_tokens.delete;
l_msg_tokens.delete;
update_serial( p_serial_number => p_wsm_serial_nums_tbl(l_index).serial_number,
p_inventory_item_id => p_item_id,
-- p_new_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_wip_entity_id => null,
p_operation_seq_num => null,
p_intraoperation_step_type => null,
x_return_status => x_return_status,
x_error_msg => x_error_msg,
x_error_count => x_error_count
);
l_msg_tokens.delete;
l_msg_tokens.delete;
elsif p_wsm_serial_nums_tbl(l_index).action_flag = WSM_UPDATE_SERIAL_NUM then
l_stmt_num := 270;
update_serial_attr ( p_calling_mode => p_calling_mode ,
p_serial_number_rec => p_wsm_serial_nums_tbl(l_index) ,
p_inventory_item_id => p_item_id ,
p_organization_id => p_organization_id ,
p_clear_serial_attr => null ,
p_wlt_txn_type => null ,
p_update_serial_attr => null ,
p_update_desc_attr => null ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
update_serial( p_serial_number => l_serial_number,
p_inventory_item_id => p_inventory_item_id,
--p_new_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_operation_seq_num,
p_intraoperation_step_type => p_intraoperation_step,
x_return_status => x_return_status,
x_error_msg => x_error_msg,
x_error_count => x_error_count
);
IF p_gen_serial_flag = 1 THEN -- Indicates that it is a generation call.. so no updates will be done...
RETURN;
update_serial_attr ( p_calling_mode => p_calling_mode ,
p_serial_number_rec => p_serial_number_rec ,
p_inventory_item_id => p_inventory_item_id ,
p_organization_id => p_organization_id ,
p_clear_serial_attr => null ,
p_wlt_txn_type => null ,
p_update_serial_attr => 1 ,
p_update_desc_attr => 1 ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Invoking update_serial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
update_serial( p_serial_number => p_serial_number,
p_inventory_item_id => p_inventory_item_id,
--p_new_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_operation_seq_num,
p_intraoperation_step_type => p_intraoperation_step,
x_return_status => x_return_status,
x_error_msg => x_error_msg,
x_error_count => x_error_count
);
select wip_entity_id,
group_mark_id,
line_mark_id,
current_status,
last_txn_source_id,
last_txn_source_type_id,
gen_object_id
into l_wip_entity_id,
l_group_mark_id,
l_line_mark_id,
l_current_status,
l_last_txn_srcid,
l_last_txn_src_typid,
l_gen_object_id
from mtl_serial_numbers
where inventory_item_id = p_assembly_item_id
and current_organization_id = p_organization_id
and serial_number = p_serial_number;
select count(1)
into l_exists
from mtl_object_genealogy mog,
mtl_serial_numbers msn
where mog.object_id = l_gen_object_id
and mog.parent_object_type = 2
and mog.parent_object_id = msn.gen_object_id
and msn.inventory_item_id = p_assembly_item_id
and msn.current_organization_id = p_organization_id
and msn.wip_entity_id = p_wip_entity_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
PROCEDURE update_serial_attr ( p_calling_mode IN NUMBER,
p_serial_number_rec IN WSM_Serial_Support_GRP.WSM_SERIAL_NUM_REC,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_clear_serial_attr IN NUMBER DEFAULT NULL, -- will be used in case of WLT to clear the attributes
p_wlt_txn_type IN NUMBER DEFAULT NULL,
-- Pass the serial attribute context corresponding to the inventory item id
p_serial_attr_context IN VARCHAR2 DEFAULT NULL,
p_update_serial_attr IN NUMBER,
p_update_desc_attr IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
)
IS
l_serial_attributes_tbl inv_lot_sel_attr.lot_sel_attributes_tbl_type;
l_update_serial_attr NUMBER;
l_update_desc_attr NUMBER;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.update_serial_attr';
l_param_tbl.delete;
l_update_serial_attr := p_update_serial_attr;
if l_update_serial_attr is null then -- the calling program hasnt passed on the value..
-- derive if a context exists...
-- need an additional check on if WMS is installed or not...
l_stmt_num := 30;
l_update_serial_attr := g_wms_installed;
l_update_serial_attr := g_wms_installed;
l_msg_tokens.delete;
p_msg_text => 'Value of l_update_serial_attr ' || l_update_serial_attr,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
if l_update_serial_attr = 1 THEN
l_stmt_num := 50;
l_msg_tokens.delete;
p_msg_text => 'Value of p_update_desc_attr ' || p_update_desc_attr,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
IF nvl(p_update_desc_attr,1) = 1 THEN
l_stmt_num := 75;
l_msg_tokens.delete;
p_msg_text => 'Invoking the procedure update_serial_attr(2)',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
update_serial_attr( p_job_serial_number => p_serial_number_rec.serial_number ,
p_inventory_item_id => p_inventory_item_id ,
p_organization_id => p_organization_id ,
p_serial_desc_attr_tbl => l_desc_attributes_tbl ,
p_attribute_category => l_attribute_category ,
p_update_serial_attr => p_update_serial_attr ,
p_update_desc_attr => 1 ,
p_serial_attributes_tbl => l_serial_attributes_tbl ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
END update_serial_attr;
PROCEDURE update_serial_attr( p_job_serial_number IN VARCHAR2,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_serial_desc_attr_tbl IN inv_serial_number_attr.char_table,
p_attribute_category IN VARCHAR2,
p_update_serial_attr IN NUMBER DEFAULT NULL,
p_update_desc_attr IN NUMBER,
p_serial_attributes_tbl IN inv_lot_sel_attr.lot_sel_attributes_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
) IS
l_validation_status varchar2(10);
l_update_serial_attr NUMBER;
l_update_desc_attr NUMBER;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.update_serial_attr(2)';
l_param_tbl.delete;
l_param_tbl(1).paramName := 'p_update_serial_attr';
l_param_tbl(1).paramValue := p_update_serial_attr;
l_param_tbl(2).paramName := 'p_update_desc_attr';
l_param_tbl(2).paramValue := p_update_desc_attr;
l_update_serial_attr := p_update_serial_attr;
l_update_desc_attr := nvl(p_update_desc_attr,1);
if l_update_serial_attr is null then -- the calling program hasnt passed on the value..
l_stmt_num := 30;
l_update_serial_attr := g_wms_installed;
l_update_serial_attr := g_wms_installed;
l_msg_tokens.delete;
p_msg_text => 'Value of l_update_serial_attr : ' || l_update_serial_attr,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
if (l_update_serial_attr = 1)
then
l_stmt_num := 50;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_SERIAL_NUMBER_PUB.validate_update_serial_att',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_SERIAL_NUMBER_PUB.validate_update_serial_att (x_return_status => x_return_status,
x_msg_count => x_error_count ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status,
p_serial_number => p_job_serial_number,
p_organization_id => p_organization_id,
p_inventory_item_id => p_inventory_item_id,
p_serial_att_tbl => p_serial_attributes_tbl,
p_validate_only => false
);
IF l_update_desc_attr = 1 then
l_stmt_num := 70;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_SERIAL_NUMBER_ATTR.Update_Serial_number_attr',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_SERIAL_NUMBER_ATTR.Update_Serial_number_attr( x_return_status => x_return_status,
x_msg_count => x_error_count,
x_msg_data => x_error_msg,
p_serial_number => p_job_serial_number,
p_inventory_item_id => p_inventory_item_id,
p_attribute_category => p_attribute_category,
p_attributes_tbl => p_serial_desc_attr_tbl
);
END update_serial_attr;
procedure update_serial( p_serial_number IN VARCHAR2,
p_inventory_item_id IN NUMBER,
--p_new_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_wip_entity_id IN NUMBER,
p_operation_seq_num IN NUMBER,
p_intraoperation_step_type IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_error_msg OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER
) is
l_object_id NUMBER;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.update_serial';
l_param_tbl.delete;
SAVEPOINT update_serial;
SELECT current_status,
initialization_date,
completion_date,
ship_date,
revision,
lot_number,
group_mark_id,
lot_line_mark_id,
current_organization_id,
current_locator_id,
current_subinventory_code,
original_wip_entity_id,
original_unit_vendor_id,
vendor_lot_number,
vendor_serial_number,
last_receipt_issue_type,
last_txn_source_id,
last_txn_source_type_id,
last_txn_source_name,
parent_item_id,
parent_serial_number
INTO l_current_status,
l_initialization_date,
l_completion_date,
l_ship_date,
l_revision,
l_lot_number,
l_group_mark_id,
l_lot_line_mark_id,
l_current_organization_id,
l_current_locator_id,
l_current_subinventory_code,
l_original_wip_entity_id,
l_original_unit_vendor_id,
l_vendor_lot_number,
l_vendor_serial_number,
l_last_receipt_issue_type,
l_last_txn_source_id,
l_last_txn_source_type_id,
l_last_txn_source_name,
l_parent_item_id,
l_parent_serial_number
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number
AND inventory_item_id = p_inventory_item_id
AND current_organization_id = p_organization_id
FOR UPDATE NOWAIT;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_SERIAL_NUMBER_PUB.updateserial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
inv_serial_number_pub.updateserial ( p_api_version => 1.0,
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id,
p_serial_number => p_serial_number,
p_initialization_date => l_initialization_date,
p_completion_date => l_completion_date,
p_ship_date => l_ship_date,
p_revision => l_revision,
p_lot_number => l_lot_number,
p_current_locator_id => l_current_locator_id,
p_subinventory_code => l_current_subinventory_code,
p_trx_src_id => l_original_wip_entity_id,
p_unit_vendor_id => l_original_unit_vendor_id,
p_vendor_lot_number => l_vendor_lot_number,
p_vendor_serial_number => l_vendor_serial_number,
p_receipt_issue_type => l_last_receipt_issue_type,
p_txn_src_id => l_last_txn_source_id,
p_txn_src_name => l_last_txn_source_name,
p_txn_src_type_id => l_last_txn_source_type_id,
p_current_status => l_current_status,
p_parent_item_id => l_parent_item_id,
p_parent_serial_number => l_parent_serial_number,
p_serial_temp_id => null,
p_last_status => l_last_status,
p_status_id => null,
x_object_id => l_object_id,
x_return_status => x_return_status,
x_msg_count => x_error_count,
x_msg_data => x_error_msg,
p_wip_entity_id => p_wip_entity_id,
p_operation_seq_num => p_operation_seq_num,
p_intraoperation_step_type => p_intraoperation_step_type,
p_line_mark_id => l_lot_line_mark_id
);
rollback to update_serial;
rollback to update_serial;
rollback to update_serial;
end update_serial;
select
wsti.Serial_Number ,
msn.gen_object_id ,
-- (Gen_object_id --> assembly_item_id No longer used Instead the column will have gen_object_id)
wsti.header_id , -- header_id
wsti.Generate_serial_number ,
wsti.Generate_for_qty ,
wsti.Action_flag ,
wsti.Current_wip_entity_name ,
wsti.Changed_wip_entity_name ,
wsti.Current_wip_entity_id ,
wsti.Changed_wip_entity_id ,
decode(wsti.serial_attribute_category , l_null_char, null, null, msn.serial_attribute_category, wsti.serial_attribute_category), -- serial_attribute_category
decode(wsti.territory_code , l_null_char, null, null, msn.territory_code , wsti.territory_code ), -- territory_code
decode(wsti.origination_date , l_null_date, null, null, msn.origination_date , wsti.origination_date ), -- origination_date
decode(wsti.c_attribute1 , l_null_char, null, null, msn.c_attribute1 , wsti.c_attribute1 ), -- c_attribute1
decode(wsti.c_attribute2 , l_null_char, null, null, msn.c_attribute2 , wsti.c_attribute2 ), -- c_attribute2
decode(wsti.c_attribute3 , l_null_char, null, null, msn.c_attribute3 , wsti.c_attribute3 ), -- c_attribute3
decode(wsti.c_attribute4 , l_null_char, null, null, msn.c_attribute4 , wsti.c_attribute4 ), -- c_attribute4
decode(wsti.c_attribute5 , l_null_char, null, null, msn.c_attribute5 , wsti.c_attribute5 ), -- c_attribute5
decode(wsti.c_attribute6 , l_null_char, null, null, msn.c_attribute6 , wsti.c_attribute6 ), -- c_attribute6
decode(wsti.c_attribute7 , l_null_char, null, null, msn.c_attribute7 , wsti.c_attribute7 ), -- c_attribute7
decode(wsti.c_attribute8 , l_null_char, null, null, msn.c_attribute8 , wsti.c_attribute8 ), -- c_attribute8
decode(wsti.c_attribute9 , l_null_char, null, null, msn.c_attribute9 , wsti.c_attribute9 ), -- c_attribute9
decode(wsti.c_attribute10 , l_null_char, null, null, msn.c_attribute10 , wsti.c_attribute10 ), -- c_attribute10
decode(wsti.c_attribute11 , l_null_char, null, null, msn.c_attribute11 , wsti.c_attribute11 ), -- c_attribute11
decode(wsti.c_attribute12 , l_null_char, null, null, msn.c_attribute12 , wsti.c_attribute12 ), -- c_attribute12
decode(wsti.c_attribute13 , l_null_char, null, null, msn.c_attribute13 , wsti.c_attribute13 ), -- c_attribute13
decode(wsti.c_attribute14 , l_null_char, null, null, msn.c_attribute14 , wsti.c_attribute14 ), -- c_attribute14
decode(wsti.c_attribute15 , l_null_char, null, null, msn.c_attribute15 , wsti.c_attribute15 ), -- c_attribute15
decode(wsti.c_attribute16 , l_null_char, null, null, msn.c_attribute16 , wsti.c_attribute16 ), -- c_attribute16
decode(wsti.c_attribute17 , l_null_char, null, null, msn.c_attribute17 , wsti.c_attribute17 ), -- c_attribute17
decode(wsti.c_attribute18 , l_null_char, null, null, msn.c_attribute18 , wsti.c_attribute18 ), -- c_attribute18
decode(wsti.c_attribute19 , l_null_char, null, null, msn.c_attribute19 , wsti.c_attribute19 ), -- c_attribute19
decode(wsti.c_attribute20 , l_null_char, null, null, msn.c_attribute20 , wsti.c_attribute20 ), -- c_attribute20
decode(wsti.d_attribute1 , l_null_date, null, null, msn.d_attribute1 , wsti.d_attribute1 ), -- d_attribute1
decode(wsti.d_attribute2 , l_null_date, null, null, msn.d_attribute2 , wsti.d_attribute2 ), -- d_attribute2
decode(wsti.d_attribute3 , l_null_date, null, null, msn.d_attribute3 , wsti.d_attribute3 ), -- d_attribute3
decode(wsti.d_attribute4 , l_null_date, null, null, msn.d_attribute4 , wsti.d_attribute4 ), -- d_attribute4
decode(wsti.d_attribute5 , l_null_date, null, null, msn.d_attribute5 , wsti.d_attribute5 ), -- d_attribute5
decode(wsti.d_attribute6 , l_null_date, null, null, msn.d_attribute6 , wsti.d_attribute6 ), -- d_attribute6
decode(wsti.d_attribute7 , l_null_date, null, null, msn.d_attribute7 , wsti.d_attribute7 ), -- d_attribute7
decode(wsti.d_attribute8 , l_null_date, null, null, msn.d_attribute8 , wsti.d_attribute8 ), -- d_attribute8
decode(wsti.d_attribute9 , l_null_date, null, null, msn.d_attribute9 , wsti.d_attribute9 ), -- d_attribute9
decode(wsti.d_attribute10 , l_null_date, null, null, msn.d_attribute10 , wsti.d_attribute10 ), -- d_attribute10
decode(wsti.n_attribute1 , l_null_num , null, null, msn.n_attribute1 , wsti.n_attribute1 ), -- n_attribute1
decode(wsti.n_attribute2 , l_null_num , null, null, msn.n_attribute2 , wsti.n_attribute2 ), -- n_attribute2
decode(wsti.n_attribute3 , l_null_num , null, null, msn.n_attribute3 , wsti.n_attribute3 ), -- n_attribute3
decode(wsti.n_attribute4 , l_null_num , null, null, msn.n_attribute4 , wsti.n_attribute4 ), -- n_attribute4
decode(wsti.n_attribute5 , l_null_num , null, null, msn.n_attribute5 , wsti.n_attribute5 ), -- n_attribute5
decode(wsti.n_attribute6 , l_null_num , null, null, msn.n_attribute6 , wsti.n_attribute6 ), -- n_attribute6
decode(wsti.n_attribute7 , l_null_num , null, null, msn.n_attribute7 , wsti.n_attribute7 ), -- n_attribute7
decode(wsti.n_attribute8 , l_null_num , null, null, msn.n_attribute8 , wsti.n_attribute8 ), -- n_attribute8
decode(wsti.n_attribute9 , l_null_num , null, null, msn.n_attribute9 , wsti.n_attribute9 ), -- n_attribute9
decode(wsti.n_attribute10 , l_null_num , null, null, msn.n_attribute10 , wsti.n_attribute10 ), -- n_attribute10
decode(wsti.status_id , l_null_num , null, null, msn.status_id , wsti.status_id ), -- status_id
decode(wsti.time_since_new , l_null_num , null, null, msn.time_since_new , wsti.time_since_new ), -- time_since_new
decode(wsti.cycles_since_new , l_null_num , null, null, msn.cycles_since_new , wsti.cycles_since_new ), -- cycles_since_new
decode(wsti.time_since_overhaul , l_null_num , null, null, msn.time_since_overhaul , wsti.time_since_overhaul ), -- time_since_overhaul
decode(wsti.cycles_since_overhaul , l_null_num , null, null, msn.cycles_since_overhaul , wsti.cycles_since_overhaul ), -- cycles_since_overhaul
decode(wsti.time_since_repair , l_null_num , null, null, msn.time_since_repair , wsti.time_since_repair ), -- time_since_repair
decode(wsti.cycles_since_repair , l_null_num , null, null, msn.cycles_since_repair , wsti.cycles_since_repair ), -- cycles_since_repair
decode(wsti.time_since_visit , l_null_num , null, null, msn.time_since_visit , wsti.time_since_visit ), -- time_since_visit
decode(wsti.cycles_since_visit , l_null_num , null, null, msn.cycles_since_visit , wsti.cycles_since_visit ), -- cycles_since_visit
decode(wsti.time_since_mark , l_null_num , null, null, msn.time_since_mark , wsti.time_since_mark ), -- time_since_mark
decode(wsti.cycles_since_mark , l_null_num , null, null, msn.cycles_since_mark , wsti.cycles_since_mark ), -- cycles_since_mark
decode(wsti.number_of_repairs , l_null_num , null, null, msn.number_of_repairs , wsti.number_of_repairs ), -- number_of_repairs
decode(wsti.attribute_category , l_null_char, l_miss_char , null ,msn.attribute_category ,wsti.attribute_category ),
decode(wsti.attribute1 , l_null_char ,l_miss_char , wsti.attribute1 ),
decode(wsti.attribute2 , l_null_char ,l_miss_char , wsti.attribute2 ),
decode(wsti.attribute3 , l_null_char ,l_miss_char , wsti.attribute3 ),
decode(wsti.attribute4 , l_null_char ,l_miss_char , wsti.attribute4 ),
decode(wsti.attribute5 , l_null_char ,l_miss_char , wsti.attribute5 ),
decode(wsti.attribute6 , l_null_char ,l_miss_char , wsti.attribute6 ),
decode(wsti.attribute7 , l_null_char ,l_miss_char , wsti.attribute7 ),
decode(wsti.attribute8 , l_null_char ,l_miss_char , wsti.attribute8 ),
decode(wsti.attribute9 , l_null_char ,l_miss_char , wsti.attribute9 ),
decode(wsti.attribute10 , l_null_char ,l_miss_char , wsti.attribute10 ),
decode(wsti.attribute11 , l_null_char ,l_miss_char , wsti.attribute11 ),
decode(wsti.attribute12 , l_null_char ,l_miss_char , wsti.attribute12 ),
decode(wsti.attribute13 , l_null_char ,l_miss_char , wsti.attribute13 ),
decode(wsti.attribute14 , l_null_char ,l_miss_char , wsti.attribute14 ),
decode(wsti.attribute15 , l_null_char ,l_miss_char , wsti.attribute15 )
from wsm_serial_txn_interface wsti,
mtl_serial_numbers msn
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 2
and (wsti.action_flag >= 5 AND wsti.action_flag <= 6) -- select the move/scrap
and wsti.serial_number = msn.serial_number (+)
and msn.inventory_item_id (+) = p_inventory_item_id
and msn.current_organization_id (+) = p_organization_id;
select
wsti.Serial_Number ,
msn.gen_object_id ,
-- (Gen_object_id --> assembly_item_id No longer used Instead the column will have gen_object_id)
wsti.header_id , -- header_id
wsti.Generate_serial_number ,
wsti.Generate_for_qty ,
wsti.Action_flag ,
wsti.Current_wip_entity_name ,
wsti.Changed_wip_entity_name ,
wsti.Current_wip_entity_id ,
wsti.Changed_wip_entity_id ,
decode(wsti.serial_attribute_category , l_null_char, null, null, msn.serial_attribute_category, wsti.serial_attribute_category), -- serial_attribute_category
decode(wsti.territory_code , l_null_char, null, null, msn.territory_code , wsti.territory_code ), -- territory_code
decode(wsti.origination_date , l_null_date, null, null, msn.origination_date , wsti.origination_date ), -- origination_date
decode(wsti.c_attribute1 , l_null_char, null, null, msn.c_attribute1 , wsti.c_attribute1 ), -- c_attribute1
decode(wsti.c_attribute2 , l_null_char, null, null, msn.c_attribute2 , wsti.c_attribute2 ), -- c_attribute2
decode(wsti.c_attribute3 , l_null_char, null, null, msn.c_attribute3 , wsti.c_attribute3 ), -- c_attribute3
decode(wsti.c_attribute4 , l_null_char, null, null, msn.c_attribute4 , wsti.c_attribute4 ), -- c_attribute4
decode(wsti.c_attribute5 , l_null_char, null, null, msn.c_attribute5 , wsti.c_attribute5 ), -- c_attribute5
decode(wsti.c_attribute6 , l_null_char, null, null, msn.c_attribute6 , wsti.c_attribute6 ), -- c_attribute6
decode(wsti.c_attribute7 , l_null_char, null, null, msn.c_attribute7 , wsti.c_attribute7 ), -- c_attribute7
decode(wsti.c_attribute8 , l_null_char, null, null, msn.c_attribute8 , wsti.c_attribute8 ), -- c_attribute8
decode(wsti.c_attribute9 , l_null_char, null, null, msn.c_attribute9 , wsti.c_attribute9 ), -- c_attribute9
decode(wsti.c_attribute10 , l_null_char, null, null, msn.c_attribute10 , wsti.c_attribute10 ), -- c_attribute10
decode(wsti.c_attribute11 , l_null_char, null, null, msn.c_attribute11 , wsti.c_attribute11 ), -- c_attribute11
decode(wsti.c_attribute12 , l_null_char, null, null, msn.c_attribute12 , wsti.c_attribute12 ), -- c_attribute12
decode(wsti.c_attribute13 , l_null_char, null, null, msn.c_attribute13 , wsti.c_attribute13 ), -- c_attribute13
decode(wsti.c_attribute14 , l_null_char, null, null, msn.c_attribute14 , wsti.c_attribute14 ), -- c_attribute14
decode(wsti.c_attribute15 , l_null_char, null, null, msn.c_attribute15 , wsti.c_attribute15 ), -- c_attribute15
decode(wsti.c_attribute16 , l_null_char, null, null, msn.c_attribute16 , wsti.c_attribute16 ), -- c_attribute16
decode(wsti.c_attribute17 , l_null_char, null, null, msn.c_attribute17 , wsti.c_attribute17 ), -- c_attribute17
decode(wsti.c_attribute18 , l_null_char, null, null, msn.c_attribute18 , wsti.c_attribute18 ), -- c_attribute18
decode(wsti.c_attribute19 , l_null_char, null, null, msn.c_attribute19 , wsti.c_attribute19 ), -- c_attribute19
decode(wsti.c_attribute20 , l_null_char, null, null, msn.c_attribute20 , wsti.c_attribute20 ), -- c_attribute20
decode(wsti.d_attribute1 , l_null_date, null, null, msn.d_attribute1 , wsti.d_attribute1 ), -- d_attribute1
decode(wsti.d_attribute2 , l_null_date, null, null, msn.d_attribute2 , wsti.d_attribute2 ), -- d_attribute2
decode(wsti.d_attribute3 , l_null_date, null, null, msn.d_attribute3 , wsti.d_attribute3 ), -- d_attribute3
decode(wsti.d_attribute4 , l_null_date, null, null, msn.d_attribute4 , wsti.d_attribute4 ), -- d_attribute4
decode(wsti.d_attribute5 , l_null_date, null, null, msn.d_attribute5 , wsti.d_attribute5 ), -- d_attribute5
decode(wsti.d_attribute6 , l_null_date, null, null, msn.d_attribute6 , wsti.d_attribute6 ), -- d_attribute6
decode(wsti.d_attribute7 , l_null_date, null, null, msn.d_attribute7 , wsti.d_attribute7 ), -- d_attribute7
decode(wsti.d_attribute8 , l_null_date, null, null, msn.d_attribute8 , wsti.d_attribute8 ), -- d_attribute8
decode(wsti.d_attribute9 , l_null_date, null, null, msn.d_attribute9 , wsti.d_attribute9 ), -- d_attribute9
decode(wsti.d_attribute10 , l_null_date, null, null, msn.d_attribute10 , wsti.d_attribute10 ), -- d_attribute10
decode(wsti.n_attribute1 , l_null_num , null, null, msn.n_attribute1 , wsti.n_attribute1 ), -- n_attribute1
decode(wsti.n_attribute2 , l_null_num , null, null, msn.n_attribute2 , wsti.n_attribute2 ), -- n_attribute2
decode(wsti.n_attribute3 , l_null_num , null, null, msn.n_attribute3 , wsti.n_attribute3 ), -- n_attribute3
decode(wsti.n_attribute4 , l_null_num , null, null, msn.n_attribute4 , wsti.n_attribute4 ), -- n_attribute4
decode(wsti.n_attribute5 , l_null_num , null, null, msn.n_attribute5 , wsti.n_attribute5 ), -- n_attribute5
decode(wsti.n_attribute6 , l_null_num , null, null, msn.n_attribute6 , wsti.n_attribute6 ), -- n_attribute6
decode(wsti.n_attribute7 , l_null_num , null, null, msn.n_attribute7 , wsti.n_attribute7 ), -- n_attribute7
decode(wsti.n_attribute8 , l_null_num , null, null, msn.n_attribute8 , wsti.n_attribute8 ), -- n_attribute8
decode(wsti.n_attribute9 , l_null_num , null, null, msn.n_attribute9 , wsti.n_attribute9 ), -- n_attribute9
decode(wsti.n_attribute10 , l_null_num , null, null, msn.n_attribute10 , wsti.n_attribute10 ), -- n_attribute10
decode(wsti.status_id , l_null_num , null, null, msn.status_id , wsti.status_id ), -- status_id
decode(wsti.time_since_new , l_null_num , null, null, msn.time_since_new , wsti.time_since_new ), -- time_since_new
decode(wsti.cycles_since_new , l_null_num , null, null, msn.cycles_since_new , wsti.cycles_since_new ), -- cycles_since_new
decode(wsti.time_since_overhaul , l_null_num , null, null, msn.time_since_overhaul , wsti.time_since_overhaul ), -- time_since_overhaul
decode(wsti.cycles_since_overhaul , l_null_num , null, null, msn.cycles_since_overhaul , wsti.cycles_since_overhaul ), -- cycles_since_overhaul
decode(wsti.time_since_repair , l_null_num , null, null, msn.time_since_repair , wsti.time_since_repair ), -- time_since_repair
decode(wsti.cycles_since_repair , l_null_num , null, null, msn.cycles_since_repair , wsti.cycles_since_repair ), -- cycles_since_repair
decode(wsti.time_since_visit , l_null_num , null, null, msn.time_since_visit , wsti.time_since_visit ), -- time_since_visit
decode(wsti.cycles_since_visit , l_null_num , null, null, msn.cycles_since_visit , wsti.cycles_since_visit ), -- cycles_since_visit
decode(wsti.time_since_mark , l_null_num , null, null, msn.time_since_mark , wsti.time_since_mark ), -- time_since_mark
decode(wsti.cycles_since_mark , l_null_num , null, null, msn.cycles_since_mark , wsti.cycles_since_mark ), -- cycles_since_mark
decode(wsti.number_of_repairs , l_null_num , null, null, msn.number_of_repairs , wsti.number_of_repairs ), -- number_of_repairs
decode(wsti.attribute_category , l_null_char, l_miss_char , null ,msn.attribute_category ,wsti.attribute_category ),
decode(wsti.attribute1 , l_null_char ,l_miss_char , wsti.attribute1 ),
decode(wsti.attribute2 , l_null_char ,l_miss_char , wsti.attribute2 ),
decode(wsti.attribute3 , l_null_char ,l_miss_char , wsti.attribute3 ),
decode(wsti.attribute4 , l_null_char ,l_miss_char , wsti.attribute4 ),
decode(wsti.attribute5 , l_null_char ,l_miss_char , wsti.attribute5 ),
decode(wsti.attribute6 , l_null_char ,l_miss_char , wsti.attribute6 ),
decode(wsti.attribute7 , l_null_char ,l_miss_char , wsti.attribute7 ),
decode(wsti.attribute8 , l_null_char ,l_miss_char , wsti.attribute8 ),
decode(wsti.attribute9 , l_null_char ,l_miss_char , wsti.attribute9 ),
decode(wsti.attribute10 , l_null_char ,l_miss_char , wsti.attribute10 ),
decode(wsti.attribute11 , l_null_char ,l_miss_char , wsti.attribute11 ),
decode(wsti.attribute12 , l_null_char ,l_miss_char , wsti.attribute12 ),
decode(wsti.attribute13 , l_null_char ,l_miss_char , wsti.attribute13 ),
decode(wsti.attribute14 , l_null_char ,l_miss_char , wsti.attribute14 ),
decode(wsti.attribute15 , l_null_char ,l_miss_char , wsti.attribute15 )
from wsm_serial_txn_interface wsti,
mtl_serial_numbers msn
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 2
and (nvl(wsti.action_flag,wsti.generate_serial_number) >= 1 AND nvl(wsti.action_flag,wsti.generate_serial_number) <= 3) -- select the move/scrap
and wsti.serial_number = msn.serial_number (+)
and msn.inventory_item_id (+) = p_inventory_item_id
and msn.current_organization_id (+) = p_organization_id
order by nvl(wsti.action_flag,0) desc; -- Code review remark
l_row_updated NUMBER;
l_param_tbl.delete;
l_row_updated := 0;
update wsm_serial_txn_interface
set process_status = wip_constants.error
where header_id = p_header_id
and transaction_type_id = 2;
l_row_updated := SQL%ROWCOUNT;
if l_row_updated > 0 OR p_wsm_serial_nums_tbl.count > 0 then
-- return error as interface rows were updated...
IF g_log_level_error >= l_log_level OR
FND_MSG_PUB.check_msg_level(G_MSG_LVL_ERROR)
THEN
l_msg_tokens.delete;
l_msg_tokens.delete;
l_wsm_serial_nums_tbl.delete;
l_row_updated := 0;
update wsm_serial_txn_interface wsti
set wsti.process_status = wip_constants.error
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 2
and wsti.action_flag in (5,6);
l_row_updated := SQL%ROWCOUNT;
if l_row_updated > 0 or p_wsm_serial_nums_tbl.count > 0 then
-- error.. message....
l_stmt_num := 80;
l_msg_tokens.delete;
update wsm_serial_txn_interface wsti
set wsti.process_status = wip_constants.error
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 2
and wsti.action_flag in (5,6)
and not exists( SELECT MSN.serial_number
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.SERIAL_NUMBER = wsti.serial_number
AND MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = p_curr_job_op_seq_num
--AND MSN.intraoperation_step_type = p_curr_job_intraop_step
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP
);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_param_tbl.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
UPDATE WSM_LOT_BASED_JOBS
SET first_serial_txn_id = p_move_txn_id
WHERE wip_entity_id = p_wip_entity_id;
UPDATE WIP_DISCRETE_JOBS
SET serialization_start_op = 10
WHERE wip_entity_id = p_wip_entity_id;
l_msg_tokens.delete;
p_msg_text => 'Updated WIP_DISCRETE_JOBS to set the serialization_start_op',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
UPDATE WSM_LOT_BASED_JOBS
set first_serial_txn_id = null
WHERE wip_entity_id = p_wip_entity_id
AND first_serial_txn_id = p_old_move_txn_id;
l_msg_tokens.delete;
p_msg_text => 'Updated ' || SQL%ROWCOUNT || ' records in WSM_LOT_BASED_JOBS',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
l_update_serial_attr NUMBER;
CURSOR c_job_serials is SELECT 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 msn.wip_entity_id = p_wip_entity_id;
l_param_tbl.delete;
select charge_jump_from_queue
into l_charge_jump_from_queue
from wsm_parameters
where organization_id = p_organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
SELECT count(*)
INTO l_job_serial_count
FROM mtl_serial_numbers MSN
where MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP;
l_msg_tokens.delete;
select wip_entity_name
into l_msg_tokens(1).TokenValue
from wip_entities
where wip_entity_id = p_wip_entity_id
and organization_id = p_organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_serial_list.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
select gen_object_id
into l_gen_object_id
from mtl_serial_numbers
where serial_number = p_serial_num_tbl(l_count).serial_number
and inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_DATE := sysdate;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY := g_user_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY_NAME := g_user_name;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_LOGIN := g_user_login_id;
l_wip_serial_nums_tbl(l_count).PROGRAM_UPDATE_DATE := sysdate;
l_msg_tokens.delete;
p_msg_text => 'Going to Insert ' || l_count || ' user provided records into wip_serial_move_interface for move and scrap',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
insert into wip_serial_move_interface values l_wip_serial_nums_tbl(l_cntr);
INSERT INTO WIP_SERIAL_MOVE_INTERFACE
( TRANSACTION_ID,
ASSEMBLY_SERIAL_NUMBER,
GEN_OBJECT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
(
SELECT
p_scrap_txn_id,
SERIAL_NUMBER,
gen_object_id,
sysdate,
g_user_id,
g_user_name,
sysdate,
g_user_id,
g_user_name,
g_user_login_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = p_curr_job_op_seq_num
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP
AND NOT EXISTS (select 'Serial Already inserted for move'
from WIP_SERIAL_MOVE_INTERFACE
where TRANSACTION_ID = p_move_txn_id
AND ASSEMBLY_SERIAL_NUMBER = MSN.SERIAL_NUMBER
)
AND NOT EXISTS (select 'Serial Already inserted for scrap'
from WIP_SERIAL_MOVE_INTERFACE
where TRANSACTION_ID = p_scrap_txn_id
AND ASSEMBLY_SERIAL_NUMBER = MSN.SERIAL_NUMBER
)
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' records into wip_serial_move_interface for scrap',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
select gen_object_id
into l_gen_object_id
from mtl_serial_numbers
where serial_number = p_serial_num_tbl(l_count).serial_number
and inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_DATE := sysdate;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY := g_user_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY_NAME := g_user_name;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_LOGIN := g_user_login_id;
l_wip_serial_nums_tbl(l_count).PROGRAM_UPDATE_DATE := sysdate;
l_msg_tokens.delete;
p_msg_text => 'Going to Insert ' || l_count || ' user provided records into wip_serial_move_interface for move and scrap',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
insert into wip_serial_move_interface values l_wip_serial_nums_tbl(l_cntr);
INSERT INTO WIP_SERIAL_MOVE_INTERFACE
( TRANSACTION_ID,
ASSEMBLY_SERIAL_NUMBER,
gen_object_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
(
SELECT
p_move_txn_id,
SERIAL_NUMBER,
gen_object_id,
sysdate,
g_user_id,
g_user_name,
sysdate,
g_user_id,
g_user_name,
g_user_login_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = p_curr_job_op_seq_num
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP
AND NOT EXISTS (select 'Serial Already inserted for move'
from WIP_SERIAL_MOVE_INTERFACE
where TRANSACTION_ID = p_move_txn_id
AND ASSEMBLY_SERIAL_NUMBER = MSN.SERIAL_NUMBER
)
AND NOT EXISTS (select 'Serial Already inserted for scrap'
from WIP_SERIAL_MOVE_INTERFACE
where TRANSACTION_ID = p_scrap_txn_id
AND ASSEMBLY_SERIAL_NUMBER = MSN.SERIAL_NUMBER
)
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' records into wip_serial_move_interface for Move',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INSERT INTO WIP_SERIAL_MOVE_INTERFACE
( TRANSACTION_ID,
ASSEMBLY_SERIAL_NUMBER,
gen_object_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
(
SELECT
p_move_txn_id,
SERIAL_NUMBER,
gen_object_id,
sysdate,
g_user_id,
g_user_name,
sysdate,
g_user_id,
g_user_name,
g_user_login_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = p_curr_job_op_seq_num
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' records into WIP_SERIAL_MOVE_INTERFACE to be moved',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INSERT INTO WIP_SERIAL_MOVE_INTERFACE
( TRANSACTION_ID,
ASSEMBLY_SERIAL_NUMBER,
gen_object_id,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_BY_NAME,
CREATION_DATE,
CREATED_BY,
CREATED_BY_NAME,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
(
SELECT
p_scrap_txn_id,
SERIAL_NUMBER,
gen_object_id,
sysdate,
g_user_id,
g_user_name,
sysdate,
g_user_id,
g_user_name,
g_user_login_id,
g_request_id,
g_program_appl_id,
g_program_id,
sysdate
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.wip_entity_id = p_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = p_curr_job_op_seq_num
AND nvl(MSN.intraoperation_step_type,-1) <> WIP_CONSTANTS.SCRAP
);
l_msg_tokens.delete;
p_msg_text => 'Inserted ' || SQL%ROWCOUNT || ' records into WIP_SERIAL_MOVE_INTERFACE to be scrapped',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
select gen_object_id
into l_gen_object_id
from mtl_serial_numbers
where serial_number = p_serial_num_tbl(l_count).serial_number
and inventory_item_id = p_inventory_item_id
and current_organization_id = p_organization_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_DATE := sysdate;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY := g_user_id;
l_wip_serial_nums_tbl(l_count).LAST_UPDATED_BY_NAME := g_user_name;
l_wip_serial_nums_tbl(l_count).LAST_UPDATE_LOGIN := g_user_login_id;
l_wip_serial_nums_tbl(l_count).PROGRAM_UPDATE_DATE := sysdate;
l_msg_tokens.delete;
p_msg_text => 'Going to insert ' || l_wip_serial_nums_tbl.count || ' records into WIP_SERIAL_MOVE_INTERFACE to be scrapped',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
insert into wip_serial_move_interface values l_wip_serial_nums_tbl(l_cntr);
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Invoking update_serial_attr(1) for ' ||
'Serial Attributes updation for serial number ' ||
p_serial_num_tbl(l_cntr).serial_number,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
update_serial_attr ( p_calling_mode => p_calling_mode,
p_serial_number_rec => p_serial_num_tbl(l_cntr),
p_inventory_item_id => p_inventory_item_id,
p_organization_id => p_organization_id ,
p_clear_serial_attr => null,
p_wlt_txn_type => null,
p_update_serial_attr => null,
p_update_desc_attr => 1,
x_return_status => x_return_status,
x_error_count => x_error_count,
x_error_msg => x_error_msg
);
l_msg_tokens.delete;
p_msg_text => 'Invoking update_serial : ' || l_job_serial_rec.serial_number
|| ' Operation : ' || p_curr_job_op_seq_num
|| ' Op Step : ' || p_curr_job_intraop_step,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
update_serial( p_serial_number => l_job_serial_rec.serial_number ,
p_inventory_item_id => p_inventory_item_id ,
p_organization_id => p_organization_id ,
p_wip_entity_id => p_wip_entity_id ,
p_operation_seq_num => p_curr_job_op_seq_num ,
p_intraoperation_step_type => p_curr_job_intraop_step ,
x_return_status => x_return_status ,
x_error_msg => x_error_count ,
x_error_count => x_error_msg
);
SELECT MSN.serial_number,
MSN.gen_object_id
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.SERIAL_NUMBER IN (select wsmt.assembly_serial_number
FROM WIP_SERIAL_MOVE_TRANSACTIONS WSMT
WHERE WSMT.transaction_id = v_txn_id
)
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
--AND MSN.current_status = WIP_CONSTANTS.IN_STORES
FOR UPDATE NOWAIT;
SELECT MSN.serial_number,
MSN.gen_object_id
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.SERIAL_NUMBER IN (select wsmt.assembly_serial_number
FROM WIP_SERIAL_MOVE_TRANSACTIONS WSMT
WHERE WSMT.transaction_id = v_txn_id
)
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = p_inventory_item_id
--AND MSN.current_status = WIP_CONSTANTS.DEF_NOT_USED
AND MSN.wip_entity_id = p_wip_entity_id
FOR UPDATE NOWAIT;
l_param_tbl.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATE_DATE := sysdate ;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATED_BY := g_user_id ;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATE_LOGIN := g_user_login_id ;
l_wip_serial_rows_tbl(l_pos).PROGRAM_UPDATE_DATE := sysdate ;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATE_DATE := sysdate ;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATED_BY := g_user_id ;
l_wip_serial_rows_tbl(l_pos).LAST_UPDATE_LOGIN := g_user_login_id ;
l_wip_serial_rows_tbl(l_pos).PROGRAM_UPDATE_DATE := sysdate ;
l_msg_tokens.delete;
p_msg_text => 'Undo Transaction : Going to insert : ' || l_wip_serial_rows_tbl.count || ' records',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
insert into wip_serial_move_interface values l_wip_serial_rows_tbl(l_counter);
select
wsti.Serial_Number ,
null ,
-- (Gen_object_id --> assembly_item_id No longer used Instead the column will have gen_object_id though the name is assembly_item_id)
wsti.header_id , -- header_id
wsti.Generate_serial_number ,
wsti.Generate_for_qty ,
wsti.Action_flag ,
wsti.Current_wip_entity_name ,
wsti.Changed_wip_entity_name ,
wsti.Current_wip_entity_id ,
wsti.Changed_wip_entity_id ,
decode(wsti.serial_attribute_category , l_null_char, null, null, msn.serial_attribute_category, wsti.serial_attribute_category), -- serial_attribute_category
decode(wsti.territory_code , l_null_char, null, null, msn.territory_code , wsti.territory_code ), -- territory_code
decode(wsti.origination_date , l_null_date, null, null, msn.origination_date , wsti.origination_date ), -- origination_date
decode(wsti.c_attribute1 , l_null_char, null, null, msn.c_attribute1 , wsti.c_attribute1 ), -- c_attribute1
decode(wsti.c_attribute2 , l_null_char, null, null, msn.c_attribute2 , wsti.c_attribute2 ), -- c_attribute2
decode(wsti.c_attribute3 , l_null_char, null, null, msn.c_attribute3 , wsti.c_attribute3 ), -- c_attribute3
decode(wsti.c_attribute4 , l_null_char, null, null, msn.c_attribute4 , wsti.c_attribute4 ), -- c_attribute4
decode(wsti.c_attribute5 , l_null_char, null, null, msn.c_attribute5 , wsti.c_attribute5 ), -- c_attribute5
decode(wsti.c_attribute6 , l_null_char, null, null, msn.c_attribute6 , wsti.c_attribute6 ), -- c_attribute6
decode(wsti.c_attribute7 , l_null_char, null, null, msn.c_attribute7 , wsti.c_attribute7 ), -- c_attribute7
decode(wsti.c_attribute8 , l_null_char, null, null, msn.c_attribute8 , wsti.c_attribute8 ), -- c_attribute8
decode(wsti.c_attribute9 , l_null_char, null, null, msn.c_attribute9 , wsti.c_attribute9 ), -- c_attribute9
decode(wsti.c_attribute10 , l_null_char, null, null, msn.c_attribute10 , wsti.c_attribute10 ), -- c_attribute10
decode(wsti.c_attribute11 , l_null_char, null, null, msn.c_attribute11 , wsti.c_attribute11 ), -- c_attribute11
decode(wsti.c_attribute12 , l_null_char, null, null, msn.c_attribute12 , wsti.c_attribute12 ), -- c_attribute12
decode(wsti.c_attribute13 , l_null_char, null, null, msn.c_attribute13 , wsti.c_attribute13 ), -- c_attribute13
decode(wsti.c_attribute14 , l_null_char, null, null, msn.c_attribute14 , wsti.c_attribute14 ), -- c_attribute14
decode(wsti.c_attribute15 , l_null_char, null, null, msn.c_attribute15 , wsti.c_attribute15 ), -- c_attribute15
decode(wsti.c_attribute16 , l_null_char, null, null, msn.c_attribute16 , wsti.c_attribute16 ), -- c_attribute16
decode(wsti.c_attribute17 , l_null_char, null, null, msn.c_attribute17 , wsti.c_attribute17 ), -- c_attribute17
decode(wsti.c_attribute18 , l_null_char, null, null, msn.c_attribute18 , wsti.c_attribute18 ), -- c_attribute18
decode(wsti.c_attribute19 , l_null_char, null, null, msn.c_attribute19 , wsti.c_attribute19 ), -- c_attribute19
decode(wsti.c_attribute20 , l_null_char, null, null, msn.c_attribute20 , wsti.c_attribute20 ), -- c_attribute20
decode(wsti.d_attribute1 , l_null_date, null, null, msn.d_attribute1 , wsti.d_attribute1 ), -- d_attribute1
decode(wsti.d_attribute2 , l_null_date, null, null, msn.d_attribute2 , wsti.d_attribute2 ), -- d_attribute2
decode(wsti.d_attribute3 , l_null_date, null, null, msn.d_attribute3 , wsti.d_attribute3 ), -- d_attribute3
decode(wsti.d_attribute4 , l_null_date, null, null, msn.d_attribute4 , wsti.d_attribute4 ), -- d_attribute4
decode(wsti.d_attribute5 , l_null_date, null, null, msn.d_attribute5 , wsti.d_attribute5 ), -- d_attribute5
decode(wsti.d_attribute6 , l_null_date, null, null, msn.d_attribute6 , wsti.d_attribute6 ), -- d_attribute6
decode(wsti.d_attribute7 , l_null_date, null, null, msn.d_attribute7 , wsti.d_attribute7 ), -- d_attribute7
decode(wsti.d_attribute8 , l_null_date, null, null, msn.d_attribute8 , wsti.d_attribute8 ), -- d_attribute8
decode(wsti.d_attribute9 , l_null_date, null, null, msn.d_attribute9 , wsti.d_attribute9 ), -- d_attribute9
decode(wsti.d_attribute10 , l_null_date, null, null, msn.d_attribute10 , wsti.d_attribute10 ), -- d_attribute10
decode(wsti.n_attribute1 , l_null_num , null, null, msn.n_attribute1 , wsti.n_attribute1 ), -- n_attribute1
decode(wsti.n_attribute2 , l_null_num , null, null, msn.n_attribute2 , wsti.n_attribute2 ), -- n_attribute2
decode(wsti.n_attribute3 , l_null_num , null, null, msn.n_attribute3 , wsti.n_attribute3 ), -- n_attribute3
decode(wsti.n_attribute4 , l_null_num , null, null, msn.n_attribute4 , wsti.n_attribute4 ), -- n_attribute4
decode(wsti.n_attribute5 , l_null_num , null, null, msn.n_attribute5 , wsti.n_attribute5 ), -- n_attribute5
decode(wsti.n_attribute6 , l_null_num , null, null, msn.n_attribute6 , wsti.n_attribute6 ), -- n_attribute6
decode(wsti.n_attribute7 , l_null_num , null, null, msn.n_attribute7 , wsti.n_attribute7 ), -- n_attribute7
decode(wsti.n_attribute8 , l_null_num , null, null, msn.n_attribute8 , wsti.n_attribute8 ), -- n_attribute8
decode(wsti.n_attribute9 , l_null_num , null, null, msn.n_attribute9 , wsti.n_attribute9 ), -- n_attribute9
decode(wsti.n_attribute10 , l_null_num , null, null, msn.n_attribute10 , wsti.n_attribute10 ), -- n_attribute10
decode(wsti.status_id , l_null_num , null, null, msn.status_id , wsti.status_id ), -- status_id
decode(wsti.time_since_new , l_null_num , null, null, msn.time_since_new , wsti.time_since_new ), -- time_since_new
decode(wsti.cycles_since_new , l_null_num , null, null, msn.cycles_since_new , wsti.cycles_since_new ), -- cycles_since_new
decode(wsti.time_since_overhaul , l_null_num , null, null, msn.time_since_overhaul , wsti.time_since_overhaul ), -- time_since_overhaul
decode(wsti.cycles_since_overhaul , l_null_num , null, null, msn.cycles_since_overhaul , wsti.cycles_since_overhaul ), -- cycles_since_overhaul
decode(wsti.time_since_repair , l_null_num , null, null, msn.time_since_repair , wsti.time_since_repair ), -- time_since_repair
decode(wsti.cycles_since_repair , l_null_num , null, null, msn.cycles_since_repair , wsti.cycles_since_repair ), -- cycles_since_repair
decode(wsti.time_since_visit , l_null_num , null, null, msn.time_since_visit , wsti.time_since_visit ), -- time_since_visit
decode(wsti.cycles_since_visit , l_null_num , null, null, msn.cycles_since_visit , wsti.cycles_since_visit ), -- cycles_since_visit
decode(wsti.time_since_mark , l_null_num , null, null, msn.time_since_mark , wsti.time_since_mark ), -- time_since_mark
decode(wsti.cycles_since_mark , l_null_num , null, null, msn.cycles_since_mark , wsti.cycles_since_mark ), -- cycles_since_mark
decode(wsti.number_of_repairs , l_null_num , null, null, msn.number_of_repairs , wsti.number_of_repairs ), -- number_of_repairs
decode(wsti.attribute_category , l_null_char, l_miss_char , null ,msn.attribute_category ,wsti.attribute_category ),
decode(wsti.attribute1 , l_null_char ,l_miss_char , wsti.attribute1 ),
decode(wsti.attribute2 , l_null_char ,l_miss_char , wsti.attribute2 ),
decode(wsti.attribute3 , l_null_char ,l_miss_char , wsti.attribute3 ),
decode(wsti.attribute4 , l_null_char ,l_miss_char , wsti.attribute4 ),
decode(wsti.attribute5 , l_null_char ,l_miss_char , wsti.attribute5 ),
decode(wsti.attribute6 , l_null_char ,l_miss_char , wsti.attribute6 ),
decode(wsti.attribute7 , l_null_char ,l_miss_char , wsti.attribute7 ),
decode(wsti.attribute8 , l_null_char ,l_miss_char , wsti.attribute8 ),
decode(wsti.attribute9 , l_null_char ,l_miss_char , wsti.attribute9 ),
decode(wsti.attribute10 , l_null_char ,l_miss_char , wsti.attribute10 ),
decode(wsti.attribute11 , l_null_char ,l_miss_char , wsti.attribute11 ),
decode(wsti.attribute12 , l_null_char ,l_miss_char , wsti.attribute12 ),
decode(wsti.attribute13 , l_null_char ,l_miss_char , wsti.attribute13 ),
decode(wsti.attribute14 , l_null_char ,l_miss_char , wsti.attribute14 ),
decode(wsti.attribute15 , l_null_char ,l_miss_char , wsti.attribute15 )
from wsm_serial_txn_interface wsti,
mtl_serial_numbers msn
where header_id = p_header_id
and transaction_type_id = 3
and wsti.serial_number = msn.serial_number (+)
and msn.inventory_item_id (+) = l_inventory_item_id
and msn.current_organization_id (+) = p_organization_id
order by nvl(wsti.action_flag,0) desc; -- Code review remark
l_param_tbl.delete;
select we.wip_entity_id,wdj.primary_item_id
into l_wip_entity_id,l_inventory_item_id
from wip_entities we,
wip_discrete_jobs wdj
where we.wip_entity_name = p_wip_entity_name
and we.wip_entity_id = wdj.wip_entity_id
and we.organization_id = p_organization_id;
l_msg_tokens.delete;
select we.wip_entity_id,wdj.primary_item_id
into l_wip_entity_id,l_inventory_item_id
from wip_entities we,
wip_discrete_jobs wdj
where we.wip_entity_name = nvl(p_wip_entity_name,we.wip_entity_name)
and we.wip_entity_id = wdj.wip_entity_id
and we.wip_entity_id = p_wip_entity_id
and we.organization_id = p_organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
update wsm_serial_txn_interface wsti
set wsti.process_status = wip_constants.error
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 3
and serial_number not in ( SELECT MSN.serial_number
FROM MTL_SERIAL_NUMBERS MSN
WHERE MSN.wip_entity_id = l_wip_entity_id
AND MSN.current_organization_id = p_organization_id
AND MSN.inventory_item_id = l_inventory_item_id
-- AND MSN.current_status = WIP_CONSTANTS.IN_STORES
-- AND MSN.operation_seq_num = l_op_seq_num
-- AND MSN.intraoperation_step_type = p_curr_job_intraop_step < not required...>
AND nvl(MSN.intraoperation_step_type,-1) <> 5
);
l_msg_tokens.delete;
elsif p_wlt_txn_type = (WSMPCNST.UPDATE_QUANTITY) THEN
l_stmt_num := 60;
update wsm_serial_txn_interface wsti
set wsti.process_status = wip_constants.error
where wsti.header_id = p_header_id
and wsti.transaction_type_id = 3
and action_flag not in (WSM_GASSOC_SERIAL_NUM,WSM_ADD_SERIAL_NUM);
l_msg_tokens.delete;
l_msg_tokens(1).TokenValue := 'Action flag for Update Quantity transaction';
select serial_number
from mtl_serial_numbers MSN,
wip_discrete_jobs WDJ
where MSN.inventory_item_id = WDJ.primary_item_id
and MSN.wip_entity_id = p_split_txn_job_id
and MSN.current_organization_id = WDJ.organization_id
and WDJ.wip_entity_id = p_split_txn_job_id
and nvl(MSN.intraoperation_step_type,-1) <> 5;
l_msg_tokens.delete;
l_msg_tokens.delete;
Procedure Insert_into_WST ( p_transaction_id IN NUMBER ,
p_transaction_type_id IN NUMBER ,
p_old_wip_entity_name IN VARCHAR2 ,
p_new_wip_entity_name IN VARCHAR2 ,
p_organization_id IN NUMBER ,
p_item_id IN NUMBER ,
p_wip_entity_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_error_msg OUT NOCOPY VARCHAR2 ,
x_error_count OUT NOCOPY NUMBER
)
IS
-- Logging variables.....
l_msg_tokens WSM_Log_PVT.token_rec_tbl;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.Insert_into_WST';
l_param_tbl.delete;
insert into wsm_serial_transactions
(transaction_id ,
transaction_type_id ,
serial_number ,
gen_object_id ,
current_wip_entity_name ,
changed_wip_entity_name ,
current_wip_entity_id ,
changed_wip_entity_id ,
created_by ,
last_update_date ,
last_updated_by ,
creation_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
original_system_reference
)
select
p_transaction_id ,
3 ,
MSN.serial_number ,
MSN.gen_object_id ,
p_old_wip_entity_name ,
p_new_wip_entity_name ,
p_wip_entity_id ,
p_wip_entity_id ,
g_user_id ,
sysdate ,
g_user_id ,
sysdate ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from mtl_serial_numbers MSN
where MSN.current_organization_id = p_organization_id
and MSN.inventory_item_id = p_item_id
and MSN.wip_entity_id = p_wip_entity_id
and nvl(MSN.intraoperation_step_type,-1) <> 5;
END Insert_into_WST;
l_update_serial_attr NUMBER;
IS SELECT msn.serial_number
FROM MTL_SERIAL_NUMBERS MSN
WHERE msn.wip_entity_id = v_wip_entity_id
AND msn.inventory_item_id = v_item_id
AND nvl(msn.INTRAOPERATION_STEP_TYPE,-1) <> WIP_CONSTANTS.SCRAP
AND msn.current_organization_id = p_organization_id;
l_param_tbl.delete;
select nvl(op_seq_num_increment, 10)
into l_op_seq_incr
from wsm_parameters
where organization_id = p_organization_id;
IF p_wlt_txn_type IN (WSMPCNST.UPDATE_ASSEMBLY,WSMPCNST.UPDATE_ROUTING) THEN
l_curr_job_op_seq_num := p_starting_jobs_tbl(l_rep_job_index).operation_seq_num + l_op_seq_incr;
l_msg_tokens.delete;
l_msg_tokens.delete;
select count(*)
into l_serial_num_count
-- ST : Fix for bug 4910758 (remove usage of wsm_job_serial_numbers_v)
-- from wsm_job_serial_numbers_v
from mtl_serial_numbers
where inventory_item_id = l_inventory_item_id
and wip_entity_id = l_wip_entity_id
and nvl(intraoperation_step_type,-1) <> 5;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_update_serial_attr := g_wms_installed;
l_update_serial_attr := g_wms_installed;
IF l_update_serial_attr = 1 then
l_parent_job_ser_context := null;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_serial_num_tbl.delete(l_index1);
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
if p_resulting_jobs_tbl(l_index).split_has_update_assy = 1 then
-- During split transaction.. user can provide attributes' information...
if l_update_serial_attr = 1 then
-- get the context of the new assembly if no context/different from the existing context then
-- populate message about the serial attributes being cleared...
l_stmt_num := 149;
l_msg_tokens.delete;
end if; -- end l_update_serial_attr = 1
l_msg_tokens.delete;
update_serial ( p_serial_number => p_serial_num_tbl(l_index1).serial_number,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => p_organization_id,
p_wip_entity_id => p_resulting_jobs_tbl(l_index).wip_entity_id,
p_operation_seq_num => l_temp_op_seq_num,
p_intraoperation_step_type => l_temp_op_step,
x_return_status => x_return_status,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
update_serial_attr ( p_calling_mode => p_calling_mode ,
p_serial_number_rec => p_serial_num_tbl(l_index1) ,
p_inventory_item_id => l_inventory_item_id ,
p_organization_id => p_organization_id ,
p_clear_serial_attr => l_clear_serial_attr ,
p_wlt_txn_type => WSMPCNST.SPLIT ,
p_update_serial_attr => l_update_serial_attr ,
p_update_desc_attr => 1 ,
p_serial_attr_context => l_child_job_ser_context ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_LOT_TRX_VALIDATION_PUB.update_item_serial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_LOT_TRX_VALIDATION_PUB.update_item_serial( x_msg_count => x_error_count ,
x_return_status => x_return_status ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status ,
p_org_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
p_to_item_id => p_resulting_jobs_tbl(l_index).primary_item_id ,
p_wip_entity_id => p_resulting_jobs_tbl(l_index).wip_entity_id ,
p_to_wip_entity_id => p_resulting_jobs_tbl(l_index).wip_entity_id ,
p_to_operation_sequence => l_temp_op_seq_num ,
p_intraoperation_step_type => l_temp_op_step
);
l_msg_tokens.delete;
update_serial ( p_serial_number => p_serial_num_tbl(l_index1).serial_number ,
p_inventory_item_id => l_inventory_item_id ,
p_organization_id => p_organization_id ,
p_wip_entity_id => p_resulting_jobs_tbl(l_index).wip_entity_id ,
p_operation_seq_num => l_curr_job_op_seq_num ,
p_intraoperation_step_type => l_curr_job_op_step ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
update_serial_attr ( p_calling_mode => p_calling_mode ,
p_serial_number_rec => p_serial_num_tbl(l_index1) ,
p_inventory_item_id => l_inventory_item_id ,
p_organization_id => p_organization_id ,
p_clear_serial_attr => l_clear_serial_attr ,
p_wlt_txn_type => WSMPCNST.SPLIT ,
p_update_serial_attr => l_update_serial_attr ,
p_update_desc_attr => 1 ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
insert into wsm_serial_transactions
(transaction_id ,
transaction_type_id ,
serial_number ,
gen_object_id ,
current_wip_entity_name ,
changed_wip_entity_name ,
current_wip_entity_id ,
changed_wip_entity_id ,
created_by ,
last_update_date ,
last_updated_by ,
creation_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
original_system_reference
)
select
p_txn_id ,
3 ,
MSN.serial_number ,
MSN.gen_object_id ,
WSSJ.wip_entity_name ,
WSRJ.wip_entity_name ,
WSSJ.wip_entity_id ,
WSRJ.wip_entity_id ,
g_user_id ,
sysdate ,
g_user_id ,
sysdate ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from mtl_serial_numbers MSN ,
wsm_sm_starting_jobs WSSJ ,
wsm_sm_resulting_jobs WSRJ
where MSN.current_organization_id = p_organization_id
and MSN.inventory_item_id = WSRJ.primary_item_id
and WSSJ.transaction_id = p_txn_id
and WSRJ.transaction_id = p_txn_id
and MSN.wip_entity_id = WSRJ.wip_entity_id
and nvl(MSN.intraoperation_step_type,-1) <> 5;
update wip_discrete_jobs
set serialization_start_op = 10
where wip_entity_id in (select wip_entity_id
from wsm_sm_resulting_jobs
where transaction_id = p_txn_id);
update wsm_lot_based_jobs
set first_serial_txn_id = -1
where wip_entity_id in (select wip_entity_id
from wsm_sm_resulting_jobs
where transaction_id = p_txn_id);
insert into wsm_serial_transactions
(transaction_id ,
transaction_type_id ,
serial_number ,
gen_object_id ,
current_wip_entity_name ,
changed_wip_entity_name ,
current_wip_entity_id ,
changed_wip_entity_id ,
created_by ,
last_update_date ,
last_updated_by ,
creation_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
original_system_reference
)
select
p_txn_id ,
3 ,
MSN.serial_number ,
MSN.gen_object_id ,
WSSJ.wip_entity_name ,
WSRJ.wip_entity_name ,
WSSJ.wip_entity_id ,
WSRJ.wip_entity_id ,
g_user_id ,
sysdate ,
g_user_id ,
sysdate ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from mtl_serial_numbers MSN ,
wsm_sm_starting_jobs WSSJ ,
wsm_sm_resulting_jobs WSRJ
where MSN.current_organization_id = p_organization_id
and MSN.inventory_item_id = WSSJ.primary_item_id
and WSSJ.transaction_id = p_txn_id
and WSRJ.transaction_id = p_txn_id
and MSN.wip_entity_id = WSSJ.wip_entity_id
and nvl(MSN.intraoperation_step_type,-1) <> 5;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_LOT_TRX_VALIDATION_PUB.update_item_serial : for Res wip_entity_id ' || p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).wip_entity_id,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_LOT_TRX_VALIDATION_PUB.update_item_serial( x_msg_count => x_error_count ,
x_return_status => x_return_status ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status ,
p_org_id => p_organization_id ,
p_item_id => p_starting_jobs_tbl(l_index).primary_item_id ,
p_to_item_id => l_inventory_item_id ,
p_wip_entity_id => p_starting_jobs_tbl(l_index).wip_entity_id ,
p_to_wip_entity_id => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).wip_entity_id,
p_to_operation_sequence => l_curr_job_op_seq_num ,
p_intraoperation_step_type => l_curr_job_op_step
);
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_LOT_TRX_VALIDATION_PUB.update_item_serial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_LOT_TRX_VALIDATION_PUB.update_item_serial( x_msg_count => x_error_count ,
x_return_status => x_return_status ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status ,
p_org_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
p_to_item_id => l_inventory_item_id ,
p_wip_entity_id => l_wip_entity_id ,
p_to_wip_entity_id => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).wip_entity_id,
p_to_operation_sequence => l_curr_job_op_seq_num ,
p_intraoperation_step_type => l_curr_job_op_step
);
update wip_discrete_jobs
set serialization_start_op = 10
where wip_entity_id in (select wip_entity_id
from wsm_sm_resulting_jobs
where transaction_id = p_txn_id);
update wsm_lot_based_jobs
set first_serial_txn_id = -1
where wip_entity_id in (select wip_entity_id
from wsm_sm_resulting_jobs
where transaction_id = p_txn_id);
ELSIF p_wlt_txn_type = WSMPCNST.UPDATE_ASSEMBLY THEN -- Update Assembly transaction
l_stmt_num := 210;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_LOT_TRX_VALIDATION_PUB.update_item_serial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_LOT_TRX_VALIDATION_PUB.update_item_serial( x_msg_count => x_error_count ,
x_return_status => x_return_status ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status ,
p_org_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
p_to_item_id => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).primary_item_id ,
p_wip_entity_id => l_wip_entity_id ,
p_to_wip_entity_id => l_wip_entity_id ,
p_to_operation_sequence => l_curr_job_op_seq_num ,
p_intraoperation_step_type => l_curr_job_op_step
);
Insert_into_WST ( p_transaction_id => p_txn_id ,
p_transaction_type_id => WSMPCNST.UPDATE_ASSEMBLY ,
p_old_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_new_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_wip_entity_id => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_id ,
p_organization_id => p_organization_id ,
p_item_id => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).primary_item_id ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
ELSIF p_wlt_txn_type = WSMPCNST.UPDATE_ROUTING THEN -- Update Routing transaction
l_stmt_num := 250;
l_msg_tokens.delete;
p_msg_text => 'Invoking INV_LOT_TRX_VALIDATION_PUB.update_item_serial',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
INV_LOT_TRX_VALIDATION_PUB.update_item_serial( x_msg_count => x_error_count ,
x_return_status => x_return_status ,
x_msg_data => x_error_msg ,
x_validation_status => l_validation_status ,
p_org_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
p_to_item_id => l_inventory_item_id ,
p_wip_entity_id => l_wip_entity_id ,
p_to_wip_entity_id => l_wip_entity_id ,
p_to_operation_sequence => l_curr_job_op_seq_num ,
p_intraoperation_step_type => l_curr_job_op_step
);
Insert_into_WST ( p_transaction_id => p_txn_id ,
p_transaction_type_id => WSMPCNST.UPDATE_ROUTING ,
p_old_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_new_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_wip_entity_id => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_id ,
p_organization_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
ELSIF p_wlt_txn_type = WSMPCNST.UPDATE_LOT_NAME THEN -- Update Lot Name transaction
l_stmt_num := 270;
Insert_into_WST ( p_transaction_id => p_txn_id ,
p_transaction_type_id => WSMPCNST.UPDATE_ROUTING ,
p_old_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_new_wip_entity_name => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).wip_entity_name ,
p_wip_entity_id => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_id ,
p_organization_id => p_organization_id ,
p_item_id => l_inventory_item_id ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
ELSIF p_wlt_txn_type = WSMPCNST.UPDATE_QUANTITY THEN -- Update Quantity transaction
l_stmt_num := 280;
l_msg_tokens.delete;
Insert_into_WST ( p_transaction_id => p_txn_id ,
p_transaction_type_id => WSMPCNST.UPDATE_QUANTITY ,
p_old_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_new_wip_entity_name => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_name ,
p_wip_entity_id => p_starting_jobs_tbl(p_starting_jobs_tbl.first).wip_entity_id ,
p_organization_id => p_organization_id ,
p_item_id => p_resulting_jobs_tbl(p_resulting_jobs_tbl.first).primary_item_id ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
select count(*)
into l_serial_num_count
-- ST : Fix for bug 4910758 (remove usage of wsm_job_serial_numbers_v)
-- from wsm_job_serial_numbers_v
from mtl_serial_numbers
where inventory_item_id = l_inventory_item_id
and wip_entity_id = l_wip_entity_id
and nvl(intraoperation_step_type,-1) <> 5;
l_msg_tokens.delete;
insert into wsm_serial_transactions
(transaction_id ,
transaction_type_id ,
serial_number ,
gen_object_id ,
current_wip_entity_name ,
changed_wip_entity_name ,
current_wip_entity_id ,
changed_wip_entity_id ,
created_by ,
last_update_date ,
last_updated_by ,
creation_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
original_system_reference
)
select
p_txn_id ,
3 ,
l_serial_tbl(l_cntr) ,
gen_object_id ,
null ,
l_wip_entity_name ,
null ,
l_wip_entity_id ,
g_user_id ,
sysdate ,
g_user_id ,
sysdate ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from mtl_serial_numbers
where serial_number = l_serial_tbl(l_cntr)
and inventory_item_id = l_inventory_item_id
and current_organization_id = p_organization_id;
insert into wsm_serial_transactions
(transaction_id ,
transaction_type_id ,
serial_number ,
gen_object_id ,
current_wip_entity_name ,
current_wip_entity_id ,
changed_wip_entity_name ,
changed_wip_entity_id ,
created_by ,
last_update_date ,
last_updated_by ,
creation_date ,
last_update_login ,
request_id ,
program_application_id ,
program_id ,
program_update_date ,
original_system_reference
)
select
p_txn_id ,
3 ,
serial_number ,
gen_object_id ,
l_wip_entity_name ,
l_wip_entity_id ,
l_wip_entity_name ,
l_wip_entity_id ,
g_user_id ,
sysdate ,
g_user_id ,
sysdate ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from mtl_serial_numbers
where inventory_item_id = l_inventory_item_id
and current_organization_id = p_organization_id
and wip_entity_id = l_wip_entity_id
and nvl(intraoperation_step_type,-1) <> 5
and serial_number NOT IN (select serial_number
from wsm_serial_transactions
where transaction_type_id = 3
and transaction_id = p_txn_id);
SELECT NVL(SERIAL_NUMBER_CONTROL_CODE,1)
INTO l_serial_cntrl_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_primary_item_id
AND organization_id = p_organization_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
l_msg_tokens.delete;
Procedure Insert_move_attr ( p_group_id IN NUMBER DEFAULT NULL,
p_move_txn_id IN NUMBER DEFAULT NULL,
p_scrap_txn_id IN NUMBER DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_error_count OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2
)
IS
-- Logging variables.....
l_msg_tokens WSM_Log_PVT.token_rec_tbl;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.Insert_move_attr';
l_param_tbl.delete;
insert into wsm_serial_txn_interface
(
HEADER_ID ,
TRANSACTION_TYPE_ID ,
SERIAL_NUMBER ,
ASSEMBLY_ITEM_ID ,
GENERATE_SERIAL_NUMBER ,
GENERATE_FOR_QTY ,
ACTION_FLAG ,
CURRENT_WIP_ENTITY_NAME ,
CHANGED_WIP_ENTITY_NAME ,
CURRENT_WIP_ENTITY_ID ,
CHANGED_WIP_ENTITY_ID ,
SERIAL_ATTRIBUTE_CATEGORY ,
TERRITORY_CODE ,
ORIGINATION_DATE ,
C_ATTRIBUTE1 ,
C_ATTRIBUTE2 ,
C_ATTRIBUTE3 ,
C_ATTRIBUTE4 ,
C_ATTRIBUTE5 ,
C_ATTRIBUTE6 ,
C_ATTRIBUTE7 ,
C_ATTRIBUTE8 ,
C_ATTRIBUTE9 ,
C_ATTRIBUTE10 ,
C_ATTRIBUTE11 ,
C_ATTRIBUTE12 ,
C_ATTRIBUTE13 ,
C_ATTRIBUTE14 ,
C_ATTRIBUTE15 ,
C_ATTRIBUTE16 ,
C_ATTRIBUTE17 ,
C_ATTRIBUTE18 ,
C_ATTRIBUTE19 ,
C_ATTRIBUTE20 ,
D_ATTRIBUTE1 ,
D_ATTRIBUTE2 ,
D_ATTRIBUTE3 ,
D_ATTRIBUTE4 ,
D_ATTRIBUTE5 ,
D_ATTRIBUTE6 ,
D_ATTRIBUTE7 ,
D_ATTRIBUTE8 ,
D_ATTRIBUTE9 ,
D_ATTRIBUTE10 ,
N_ATTRIBUTE1 ,
N_ATTRIBUTE2 ,
N_ATTRIBUTE3 ,
N_ATTRIBUTE4 ,
N_ATTRIBUTE5 ,
N_ATTRIBUTE6 ,
N_ATTRIBUTE7 ,
N_ATTRIBUTE8 ,
N_ATTRIBUTE9 ,
N_ATTRIBUTE10 ,
STATUS_ID ,
TIME_SINCE_NEW ,
CYCLES_SINCE_NEW ,
TIME_SINCE_OVERHAUL ,
CYCLES_SINCE_OVERHAUL ,
TIME_SINCE_REPAIR ,
CYCLES_SINCE_REPAIR ,
TIME_SINCE_VISIT ,
CYCLES_SINCE_VISIT ,
TIME_SINCE_MARK ,
CYCLES_SINCE_MARK ,
NUMBER_OF_REPAIRS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ORIGINAL_SYSTEM_REFERENCE
)
Select
WMTI.transaction_id ,
5 , -- for internal use... 5 for Attributes updation..
MSN.SERIAL_NUMBER ,
WMTI.primary_item_id ,
null ,
null ,
null ,
null ,
null ,
WMTI.WIP_ENTITY_ID ,
null ,
MSN.SERIAL_ATTRIBUTE_CATEGORY ,
MSN.TERRITORY_CODE ,
MSN.ORIGINATION_DATE ,
MSN.C_ATTRIBUTE1 ,
MSN.C_ATTRIBUTE2 ,
MSN.C_ATTRIBUTE3 ,
MSN.C_ATTRIBUTE4 ,
MSN.C_ATTRIBUTE5 ,
MSN.C_ATTRIBUTE6 ,
MSN.C_ATTRIBUTE7 ,
MSN.C_ATTRIBUTE8 ,
MSN.C_ATTRIBUTE9 ,
MSN.C_ATTRIBUTE10 ,
MSN.C_ATTRIBUTE11 ,
MSN.C_ATTRIBUTE12 ,
MSN.C_ATTRIBUTE13 ,
MSN.C_ATTRIBUTE14 ,
MSN.C_ATTRIBUTE15 ,
MSN.C_ATTRIBUTE16 ,
MSN.C_ATTRIBUTE17 ,
MSN.C_ATTRIBUTE18 ,
MSN.C_ATTRIBUTE19 ,
MSN.C_ATTRIBUTE20 ,
MSN.D_ATTRIBUTE1 ,
MSN.D_ATTRIBUTE2 ,
MSN.D_ATTRIBUTE3 ,
MSN.D_ATTRIBUTE4 ,
MSN.D_ATTRIBUTE5 ,
MSN.D_ATTRIBUTE6 ,
MSN.D_ATTRIBUTE7 ,
MSN.D_ATTRIBUTE8 ,
MSN.D_ATTRIBUTE9 ,
MSN.D_ATTRIBUTE10 ,
MSN.N_ATTRIBUTE1 ,
MSN.N_ATTRIBUTE2 ,
MSN.N_ATTRIBUTE3 ,
MSN.N_ATTRIBUTE4 ,
MSN.N_ATTRIBUTE5 ,
MSN.N_ATTRIBUTE6 ,
MSN.N_ATTRIBUTE7 ,
MSN.N_ATTRIBUTE8 ,
MSN.N_ATTRIBUTE9 ,
MSN.N_ATTRIBUTE10 ,
MSN.STATUS_ID ,
MSN.TIME_SINCE_NEW ,
MSN.CYCLES_SINCE_NEW ,
MSN.TIME_SINCE_OVERHAUL ,
MSN.CYCLES_SINCE_OVERHAUL ,
MSN.TIME_SINCE_REPAIR ,
MSN.CYCLES_SINCE_REPAIR ,
MSN.TIME_SINCE_VISIT ,
MSN.CYCLES_SINCE_VISIT ,
MSN.TIME_SINCE_MARK ,
MSN.CYCLES_SINCE_MARK ,
MSN.NUMBER_OF_REPAIRS ,
MSN.ATTRIBUTE_CATEGORY ,
MSN.ATTRIBUTE1 ,
MSN.ATTRIBUTE2 ,
MSN.ATTRIBUTE3 ,
MSN.ATTRIBUTE4 ,
MSN.ATTRIBUTE5 ,
MSN.ATTRIBUTE6 ,
MSN.ATTRIBUTE7 ,
MSN.ATTRIBUTE8 ,
MSN.ATTRIBUTE9 ,
MSN.ATTRIBUTE10 ,
MSN.ATTRIBUTE11 ,
MSN.ATTRIBUTE12 ,
MSN.ATTRIBUTE13 ,
MSN.ATTRIBUTE14 ,
MSN.ATTRIBUTE15 ,
g_user_id ,
SYSDATE ,
g_user_id ,
SYSDATE ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from wip_serial_move_interface WSMI,
mtl_serial_numbers MSN,
wip_move_txn_interface WMTI
where WMTI.group_id = p_group_id
and WMTI.transaction_id = WSMI.transaction_id
and MSN.serial_number = WSMI.assembly_serial_number
and MSN.current_organization_id = WMTI.organization_id
and MSN.inventory_item_id = WMTI.primary_item_id
-- ST : Commenting it out...
-- and MSN.serial_attribute_category IS NOT NULL -- Desc flex fields are not cleared. Only serial attributes
and WMTI.transaction_type IN (2,3); -- Completion/Assembly return
l_msg_tokens.delete;
p_msg_text => 'Total Serial Numbers inserted : ' || SQL%ROWCOUNT,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
insert into wsm_serial_txn_temp
(
HEADER_ID ,
TRANSACTION_TYPE_ID ,
SERIAL_NUMBER ,
ASSEMBLY_ITEM_ID ,
GENERATE_SERIAL_NUMBER ,
GENERATE_FOR_QTY ,
ACTION_FLAG ,
CURRENT_WIP_ENTITY_NAME ,
CHANGED_WIP_ENTITY_NAME ,
CURRENT_WIP_ENTITY_ID ,
CHANGED_WIP_ENTITY_ID ,
SERIAL_ATTRIBUTE_CATEGORY ,
TERRITORY_CODE ,
ORIGINATION_DATE ,
C_ATTRIBUTE1 ,
C_ATTRIBUTE2 ,
C_ATTRIBUTE3 ,
C_ATTRIBUTE4 ,
C_ATTRIBUTE5 ,
C_ATTRIBUTE6 ,
C_ATTRIBUTE7 ,
C_ATTRIBUTE8 ,
C_ATTRIBUTE9 ,
C_ATTRIBUTE10 ,
C_ATTRIBUTE11 ,
C_ATTRIBUTE12 ,
C_ATTRIBUTE13 ,
C_ATTRIBUTE14 ,
C_ATTRIBUTE15 ,
C_ATTRIBUTE16 ,
C_ATTRIBUTE17 ,
C_ATTRIBUTE18 ,
C_ATTRIBUTE19 ,
C_ATTRIBUTE20 ,
D_ATTRIBUTE1 ,
D_ATTRIBUTE2 ,
D_ATTRIBUTE3 ,
D_ATTRIBUTE4 ,
D_ATTRIBUTE5 ,
D_ATTRIBUTE6 ,
D_ATTRIBUTE7 ,
D_ATTRIBUTE8 ,
D_ATTRIBUTE9 ,
D_ATTRIBUTE10 ,
N_ATTRIBUTE1 ,
N_ATTRIBUTE2 ,
N_ATTRIBUTE3 ,
N_ATTRIBUTE4 ,
N_ATTRIBUTE5 ,
N_ATTRIBUTE6 ,
N_ATTRIBUTE7 ,
N_ATTRIBUTE8 ,
N_ATTRIBUTE9 ,
N_ATTRIBUTE10 ,
STATUS_ID ,
TIME_SINCE_NEW ,
CYCLES_SINCE_NEW ,
TIME_SINCE_OVERHAUL ,
CYCLES_SINCE_OVERHAUL ,
TIME_SINCE_REPAIR ,
CYCLES_SINCE_REPAIR ,
TIME_SINCE_VISIT ,
CYCLES_SINCE_VISIT ,
TIME_SINCE_MARK ,
CYCLES_SINCE_MARK ,
NUMBER_OF_REPAIRS ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
ORIGINAL_SYSTEM_REFERENCE
)
Select
WMTI.transaction_id ,
5 , -- for internal use... 5 for Attributes updation..
MSN.SERIAL_NUMBER ,
WMTI.primary_item_id ,
null ,
null ,
null ,
null ,
null ,
WMTI.WIP_ENTITY_ID ,
null ,
MSN.SERIAL_ATTRIBUTE_CATEGORY ,
MSN.TERRITORY_CODE ,
MSN.ORIGINATION_DATE ,
MSN.C_ATTRIBUTE1 ,
MSN.C_ATTRIBUTE2 ,
MSN.C_ATTRIBUTE3 ,
MSN.C_ATTRIBUTE4 ,
MSN.C_ATTRIBUTE5 ,
MSN.C_ATTRIBUTE6 ,
MSN.C_ATTRIBUTE7 ,
MSN.C_ATTRIBUTE8 ,
MSN.C_ATTRIBUTE9 ,
MSN.C_ATTRIBUTE10 ,
MSN.C_ATTRIBUTE11 ,
MSN.C_ATTRIBUTE12 ,
MSN.C_ATTRIBUTE13 ,
MSN.C_ATTRIBUTE14 ,
MSN.C_ATTRIBUTE15 ,
MSN.C_ATTRIBUTE16 ,
MSN.C_ATTRIBUTE17 ,
MSN.C_ATTRIBUTE18 ,
MSN.C_ATTRIBUTE19 ,
MSN.C_ATTRIBUTE20 ,
MSN.D_ATTRIBUTE1 ,
MSN.D_ATTRIBUTE2 ,
MSN.D_ATTRIBUTE3 ,
MSN.D_ATTRIBUTE4 ,
MSN.D_ATTRIBUTE5 ,
MSN.D_ATTRIBUTE6 ,
MSN.D_ATTRIBUTE7 ,
MSN.D_ATTRIBUTE8 ,
MSN.D_ATTRIBUTE9 ,
MSN.D_ATTRIBUTE10 ,
MSN.N_ATTRIBUTE1 ,
MSN.N_ATTRIBUTE2 ,
MSN.N_ATTRIBUTE3 ,
MSN.N_ATTRIBUTE4 ,
MSN.N_ATTRIBUTE5 ,
MSN.N_ATTRIBUTE6 ,
MSN.N_ATTRIBUTE7 ,
MSN.N_ATTRIBUTE8 ,
MSN.N_ATTRIBUTE9 ,
MSN.N_ATTRIBUTE10 ,
MSN.STATUS_ID ,
MSN.TIME_SINCE_NEW ,
MSN.CYCLES_SINCE_NEW ,
MSN.TIME_SINCE_OVERHAUL ,
MSN.CYCLES_SINCE_OVERHAUL ,
MSN.TIME_SINCE_REPAIR ,
MSN.CYCLES_SINCE_REPAIR ,
MSN.TIME_SINCE_VISIT ,
MSN.CYCLES_SINCE_VISIT ,
MSN.TIME_SINCE_MARK ,
MSN.CYCLES_SINCE_MARK ,
MSN.NUMBER_OF_REPAIRS ,
MSN.ATTRIBUTE_CATEGORY ,
MSN.ATTRIBUTE1 ,
MSN.ATTRIBUTE2 ,
MSN.ATTRIBUTE3 ,
MSN.ATTRIBUTE4 ,
MSN.ATTRIBUTE5 ,
MSN.ATTRIBUTE6 ,
MSN.ATTRIBUTE7 ,
MSN.ATTRIBUTE8 ,
MSN.ATTRIBUTE9 ,
MSN.ATTRIBUTE10 ,
MSN.ATTRIBUTE11 ,
MSN.ATTRIBUTE12 ,
MSN.ATTRIBUTE13 ,
MSN.ATTRIBUTE14 ,
MSN.ATTRIBUTE15 ,
g_user_id ,
SYSDATE ,
g_user_id ,
SYSDATE ,
g_user_login_id ,
g_request_id ,
g_program_appl_id ,
g_program_id ,
sysdate ,
null
from wip_serial_move_interface WSMI,
mtl_serial_numbers MSN,
wip_move_txn_interface WMTI
where WMTI.transaction_id in (p_move_txn_id,p_scrap_txn_id)
and WMTI.transaction_id = WSMI.transaction_id
and MSN.serial_number = WSMI.assembly_serial_number
and MSN.current_organization_id = WMTI.organization_id
and MSN.inventory_item_id = WMTI.primary_item_id
-- ST : Commenting it out...
-- and MSN.serial_attribute_category IS NOT NULL -- Desc flex fields are not cleared. Only serial attributes
and WMTI.transaction_type IN (2,3); -- Completion/Assembly return
l_msg_tokens.delete;
p_msg_text => 'Total Serial Numbers inserted : ' || SQL%ROWCOUNT,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
END Insert_move_attr;
Procedure Update_attr_move ( p_group_id IN NUMBER DEFAULT NULL, -- for interface...
p_internal_group_id IN NUMBER DEFAULT NULL, -- for interface...
p_move_txn_id IN NUMBER DEFAULT NULL, -- for forms...
p_scrap_txn_id IN NUMBER DEFAULT NULL, -- for forms...
p_organization_id IN NUMBER ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_error_count OUT NOCOPY NUMBER ,
x_error_msg OUT NOCOPY VARCHAR2
)
IS
-- This cursor fetches the serials whose op info need to be cleared...but havent been cleared by WIP
-- Possible when the serial tracking doesnt start with the move tranaction's from op seq num as 10 and step as Queue
-- This is because we fill the serial start op as 10 always in WDJ
-- So the reverse doesnt happen (ie) WIP clears the op info when it has to be present as there is
-- no possible step prior to 10 Queue
-- Or it is also possible if a pure scrap transaction triggered the serial tracking.
cursor c_serials_intf is
select msn.serial_number,
msn.inventory_item_id,
msn.current_organization_id,
wmt.wip_entity_id
from mtl_serial_numbers msn,
wip_move_transactions wmt,
wsm_lot_move_txn_interface wlmti,
wsm_lot_based_jobs wlbj
where wlmti.group_id = p_group_id
and wlmti.internal_group_id = p_internal_group_id
and wlmti.wip_entity_id = wmt.wip_entity_id
and wmt.group_id = p_internal_group_id
and wlmti.status = WIP_CONSTANTS.COMPLETED
and wmt.wip_entity_id = wlbj.wip_entity_id
and wlbj.first_serial_txn_id IS NULL
and msn.inventory_item_id = wmt.primary_item_id
and msn.current_organization_id = wmt.organization_id
and msn.wip_entity_id = wmt.wip_entity_id
and msn.operation_seq_num IS NOT NULL;
select msn.serial_number,
msn.inventory_item_id,
msn.current_organization_id,
wdj.wip_entity_id
from mtl_serial_numbers msn,
wsm_lot_move_txn_interface wlmti,
wip_discrete_jobs wdj,
wsm_lot_based_jobs wlbj
where wlmti.group_id = p_group_id
and wlmti.internal_group_id = p_internal_group_id
and wlmti.wip_entity_id = wdj.wip_entity_id
and wlmti.status = WIP_CONSTANTS.COMPLETED
and wdj.wip_entity_id = wlbj.wip_entity_id
and wlbj.first_serial_txn_id = -1 -- It will be set to -1 only for those jobs created through Lot Creation form..
and msn.inventory_item_id = wdj.primary_item_id
and msn.current_organization_id = wdj.organization_id
and msn.wip_entity_id = wdj.wip_entity_id
and msn.operation_seq_num IS NULL; -- This will be set when the jobs created Lot Creation reach operation 10..
select msn.serial_number,
msn.inventory_item_id,
msn.current_organization_id,
wmt.wip_entity_id
from mtl_serial_numbers msn,
wip_move_transactions wmt,
wsm_lot_based_jobs wlbj
where wmt.transaction_id in (p_move_txn_id, p_scrap_txn_id)
and wmt.wip_entity_id = wlbj.wip_entity_id
and wlbj.first_serial_txn_id IS NULL
and msn.inventory_item_id = wmt.primary_item_id
and msn.current_organization_id = wmt.organization_id
and msn.wip_entity_id = wmt.wip_entity_id
and msn.operation_seq_num IS NOT NULL;
select msn.serial_number,
msn.inventory_item_id,
msn.current_organization_id,
wmt.wip_entity_id
from mtl_serial_numbers msn,
wip_move_transactions wmt,
wip_serial_move_transactions wsmt,
wsm_lot_based_jobs wlbj
where wmt.transaction_id in (p_move_txn_id, p_scrap_txn_id)
and wmt.wip_entity_id = wlbj.wip_entity_id
and wmt.transaction_id = wsmt.transaction_id
and wlbj.first_serial_txn_id = -1 -- It will be set to -1 only for those jobs created through Lot Creation form..
and msn.serial_number = wsmt.assembly_serial_number
and msn.inventory_item_id = wmt.primary_item_id
and msn.current_organization_id = wmt.organization_id
and msn.wip_entity_id = wmt.wip_entity_id
and msn.operation_seq_num IS NULL;
select
Serial_Number ,
assembly_item_id , -- assembly_item_id
header_id , -- header_id
Generate_serial_number ,
Generate_for_qty ,
Action_flag ,
Current_wip_entity_name ,
Changed_wip_entity_name ,
Current_wip_entity_id ,
Changed_wip_entity_id ,
serial_attribute_category ,
territory_code ,
origination_date ,
c_attribute1 ,
c_attribute2 ,
c_attribute3 ,
c_attribute4 ,
c_attribute5 ,
c_attribute6 ,
c_attribute7 ,
c_attribute8 ,
c_attribute9 ,
c_attribute10 ,
c_attribute11 ,
c_attribute12 ,
c_attribute13 ,
c_attribute14 ,
c_attribute15 ,
c_attribute16 ,
c_attribute17 ,
c_attribute18 ,
c_attribute19 ,
c_attribute20 ,
d_attribute1 ,
d_attribute2 ,
d_attribute3 ,
d_attribute4 ,
d_attribute5 ,
d_attribute6 ,
d_attribute7 ,
d_attribute8 ,
d_attribute9 ,
d_attribute10 ,
n_attribute1 ,
n_attribute2 ,
n_attribute3 ,
n_attribute4 ,
n_attribute5 ,
n_attribute6 ,
n_attribute7 ,
n_attribute8 ,
n_attribute9 ,
n_attribute10 ,
status_id ,
time_since_new ,
cycles_since_new ,
time_since_overhaul ,
cycles_since_overhaul ,
time_since_repair ,
cycles_since_repair ,
time_since_visit ,
cycles_since_visit ,
time_since_mark ,
cycles_since_mark ,
number_of_repairs ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from wsm_serial_txn_interface wsti
where header_id IN (Select wmt.transaction_id
from wip_move_transactions wmt,
wsm_lot_move_txn_interface wlmti
where wlmti.group_id = p_group_id
and wlmti.internal_group_id = p_internal_group_id
and wlmti.wip_entity_id = wmt.wip_entity_id
and wlmti.status = WIP_CONSTANTS.COMPLETED
)
and transaction_type_id = 5;
select
Serial_Number ,
assembly_item_id , -- assembly_item_id
header_id , -- header_id
Generate_serial_number ,
Generate_for_qty ,
Action_flag ,
Current_wip_entity_name ,
Changed_wip_entity_name ,
Current_wip_entity_id ,
Changed_wip_entity_id ,
serial_attribute_category ,
territory_code ,
origination_date ,
c_attribute1 ,
c_attribute2 ,
c_attribute3 ,
c_attribute4 ,
c_attribute5 ,
c_attribute6 ,
c_attribute7 ,
c_attribute8 ,
c_attribute9 ,
c_attribute10 ,
c_attribute11 ,
c_attribute12 ,
c_attribute13 ,
c_attribute14 ,
c_attribute15 ,
c_attribute16 ,
c_attribute17 ,
c_attribute18 ,
c_attribute19 ,
c_attribute20 ,
d_attribute1 ,
d_attribute2 ,
d_attribute3 ,
d_attribute4 ,
d_attribute5 ,
d_attribute6 ,
d_attribute7 ,
d_attribute8 ,
d_attribute9 ,
d_attribute10 ,
n_attribute1 ,
n_attribute2 ,
n_attribute3 ,
n_attribute4 ,
n_attribute5 ,
n_attribute6 ,
n_attribute7 ,
n_attribute8 ,
n_attribute9 ,
n_attribute10 ,
status_id ,
time_since_new ,
cycles_since_new ,
time_since_overhaul ,
cycles_since_overhaul ,
time_since_repair ,
cycles_since_repair ,
time_since_visit ,
cycles_since_visit ,
time_since_mark ,
cycles_since_mark ,
number_of_repairs ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from wsm_serial_txn_temp wsti
where header_id IN (p_move_txn_id, p_scrap_txn_id)
and transaction_type_id = 5;
l_module VARCHAR2(100) := 'wsm.plsql.WSM_SERIAL_SUPPORT_PVT.update_attr_move';
l_param_tbl.delete;
l_msg_tokens.delete;
update_serial( p_serial_number => l_serial_info_tbl(l_cntr).serial_number ,
p_inventory_item_id => l_serial_info_tbl(l_cntr).inventory_item_id ,
p_organization_id => l_serial_info_tbl(l_cntr).organization_id ,
p_wip_entity_id => l_serial_info_tbl(l_cntr).wip_entity_id ,
p_operation_seq_num => null ,
p_intraoperation_step_type => null ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
l_msg_tokens.delete;
update_serial( p_serial_number => l_serial_info_tbl(l_cntr).serial_number ,
p_inventory_item_id => l_serial_info_tbl(l_cntr).inventory_item_id ,
p_organization_id => l_serial_info_tbl(l_cntr).organization_id ,
p_wip_entity_id => l_serial_info_tbl(l_cntr).wip_entity_id ,
p_operation_seq_num => null ,
p_intraoperation_step_type => null ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
l_msg_tokens.delete;
update_serial( p_serial_number => l_serial_info_tbl(l_cntr).serial_number ,
p_inventory_item_id => l_serial_info_tbl(l_cntr).inventory_item_id ,
p_organization_id => l_serial_info_tbl(l_cntr).organization_id ,
p_wip_entity_id => l_serial_info_tbl(l_cntr).wip_entity_id ,
p_operation_seq_num => 10 ,
p_intraoperation_step_type => WIP_CONSTANTS.QUEUE ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
l_msg_tokens.delete;
update_serial( p_serial_number => l_serial_info_tbl(l_cntr).serial_number ,
p_inventory_item_id => l_serial_info_tbl(l_cntr).inventory_item_id ,
p_organization_id => l_serial_info_tbl(l_cntr).organization_id ,
p_wip_entity_id => l_serial_info_tbl(l_cntr).wip_entity_id ,
p_operation_seq_num => 10 ,
p_intraoperation_step_type => WIP_CONSTANTS.QUEUE ,
x_return_status => x_return_status ,
x_error_msg => x_error_msg ,
x_error_count => x_error_count
);
l_msg_tokens.delete;
update_serial_attr ( p_calling_mode => 1 ,
p_serial_number_rec => l_wsm_serial_attrs_tbl(l_cntr) ,
p_inventory_item_id => l_wsm_serial_attrs_tbl(l_cntr).assembly_item_id ,
p_organization_id => nvl(p_organization_id,l_wsm_serial_attrs_tbl(l_cntr).action_flag) ,
p_clear_serial_attr => null ,
p_wlt_txn_type => null ,
p_update_serial_attr => 1 ,
p_update_desc_attr => null ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
delete from wsm_serial_txn_interface
where header_id = l_serial_txn_tbl(l_header)
and transaction_type_id = 5;
l_msg_tokens.delete;
update_serial_attr ( p_calling_mode => 2 ,
p_serial_number_rec => l_wsm_serial_attrs_tbl(l_cntr) ,
p_inventory_item_id => l_wsm_serial_attrs_tbl(l_cntr).assembly_item_id ,
p_organization_id => p_organization_id ,
p_clear_serial_attr => null ,
p_wlt_txn_type => null ,
p_update_serial_attr => 1 ,
p_update_desc_attr => null ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
delete from wsm_serial_txn_temp
where header_id IN (p_move_txn_id,p_scrap_txn_id)
and transaction_type_id = 5;
l_msg_tokens.delete;
p_msg_text => 'Deleted ' || SQL%ROWCOUNT || ' records inserted for attributes reason',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
l_msg_tokens.delete;
END Update_attr_move;
select
Serial_Number ,
gen_object_id , -- (Gen_object_id --> assembly_item_id No longer used Instead the column will have gen_object_id)
header_id , -- header_id
Generate_serial_number ,
Generate_for_qty ,
Action_flag ,
Current_wip_entity_name ,
Changed_wip_entity_name ,
Current_wip_entity_id ,
Changed_wip_entity_id ,
serial_attribute_category ,
territory_code ,
origination_date ,
c_attribute1 ,
c_attribute2 ,
c_attribute3 ,
c_attribute4 ,
c_attribute5 ,
c_attribute6 ,
c_attribute7 ,
c_attribute8 ,
c_attribute9 ,
c_attribute10 ,
c_attribute11 ,
c_attribute12 ,
c_attribute13 ,
c_attribute14 ,
c_attribute15 ,
c_attribute16 ,
c_attribute17 ,
c_attribute18 ,
c_attribute19 ,
c_attribute20 ,
d_attribute1 ,
d_attribute2 ,
d_attribute3 ,
d_attribute4 ,
d_attribute5 ,
d_attribute6 ,
d_attribute7 ,
d_attribute8 ,
d_attribute9 ,
d_attribute10 ,
n_attribute1 ,
n_attribute2 ,
n_attribute3 ,
n_attribute4 ,
n_attribute5 ,
n_attribute6 ,
n_attribute7 ,
n_attribute8 ,
n_attribute9 ,
n_attribute10 ,
status_id ,
time_since_new ,
cycles_since_new ,
time_since_overhaul ,
cycles_since_overhaul ,
time_since_repair ,
cycles_since_repair ,
time_since_visit ,
cycles_since_visit ,
time_since_mark ,
cycles_since_mark ,
number_of_repairs ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
from wsm_serial_txn_temp
where header_id = p_move_txn_id
and transaction_type_id = 2
and action_flag IN (5,6); -- select the move/scrap
l_msg_tokens.delete;
l_msg_tokens.delete;
select max(wmt.transaction_id)
into l_old_move_txn_id
from wip_move_transactions wmt
where wmt.organization_id = p_organization_id
and wmt.wip_entity_id = p_wip_entity_id
and wmt.wsm_undo_txn_id IS NULL
and wmt.transaction_id = wmt.batch_id;
select max(transaction_id)
into l_old_scrap_txn_id
from wip_move_transactions
where organization_id = p_organization_id
and wip_entity_id = p_wip_entity_id
and batch_id = l_old_move_txn_id
and transaction_id <> batch_id;
l_msg_tokens.delete;
l_msg_tokens.delete;
p_msg_text => 'Invoking WSM_Serial_support_PVT.Insert_move_attr',
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens ,
p_fnd_log_level => G_LOG_LEVEL_STATEMENT ,
p_run_log_level => l_log_level
);
WSM_Serial_support_PVT.Insert_move_attr ( p_group_id => null ,
p_move_txn_id => p_move_txn_id ,
p_scrap_txn_id => p_scrap_txn_id ,
x_return_status => x_return_status ,
x_error_count => x_error_count ,
x_error_msg => x_error_msg
);
l_msg_tokens.delete;
l_param_tbl.delete;
l_message_tbl.delete;