[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_wsm_rsv_v_tbl.delete;
If p_txn_header.transaction_type_id = WSMPCNST.UPDATE_ASSEMBLY then
--Write to WIE 'Note: Starting job is reserved against sales order(s). Update Assembly transaction
--will result in deletion of the starting job reservations
--Write warning into concurrent log
fnd_message.set_name('WSM','WSM_RSV_UPD_ASSY');
--MP Delete Changes Start
BEGIN
select *
bulk collect into l_wsm_rsv_v_tbl
from wsm_reservations_v
where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
--MP Delete Changes End
If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop --MP Delete Changes
l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_old
, p_serial_number => l_dummy_sn
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
p_msg_text => 'inv_reservation_pub.delete_reservation failed',
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 loop; --MP Delete Changes
end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
--If yes, then check if there is update of assembly.
--If yes, then write to WIE 'Note: Starting job is reserved against sales order(s).Update Assembly transaction will result in deletion of the starting job reservations'
--and call inv_reservation_pub.delete_reservation
--Else, compare starting job's net qty with net qty of same job in resulting jobs.
--Write to WIE 'Note: New net quantity is less than the starting job's reserved quantity.This transaction will result in reduction in reserved quantity' and Call reduce_reservations( );
--if parent job is not a resulting job, then write to WIE 'Note: Starting job isnot a resulting job.The reservations against sales order(s) for this job will be deleted..
If (p_sj_also_rj_index is not null) then
If p_resulting_jobs_tbl(p_sj_also_rj_index).split_has_update_assy = 1 then
--Write warning into concurrent log
fnd_message.set_name('WSM','WSM_RSV_UPD_ASSY');
--MP Delete Changes Start
BEGIN
select *
bulk collect into l_wsm_rsv_v_tbl
from wsm_reservations_v
where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
--MP Delete Changes End
--MP Delete Changes
If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_old
, p_serial_number => l_dummy_sn
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
p_msg_text => 'inv_reservation_pub.delete_reservation failed',
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 loop; --MP delete reservations loop
end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
select primary_uom_code
into l_expected_quantity_uom --l_mtl_rsv_rec.expected_quantity_uom
from mtl_system_items
where inventory_item_id =l_mtl_rsv_rec.inventory_item_id-- p_starting_jobs_tbl(p_starting_jobs_tbl.first).primary_item_id
and organization_id = l_mtl_rsv_rec.organization_id; --p_starting_jobs_tbl(p_starting_jobs_tbl.first).organization_id;
, p_delete_flag => 'N'
, p_sort_by_criteria => null
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
--MP Delete Changes Start
BEGIN
select *
bulk collect into l_wsm_rsv_v_tbl
from wsm_reservations_v
where wip_entity_id = p_starting_jobs_tbl(p_rep_job_index).wip_entity_id;
--MP Delete Changes End
--MP Delete Changes
If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_old
, p_serial_number => l_dummy_sn
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
p_msg_text => 'inv_reservation_pub.delete_reservation failed',
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 loop; --MP delete reservations loop
end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
update wip_discrete_jobs
set net_quantity = start_quantity
where wip_entity_id = p_resulting_jobs_tbl(l_rj_index).wip_entity_id;
select *
bulk collect into l_wsm_rsv_v_tbl
from wsm_reservations_v
where wip_entity_id = p_starting_jobs_tbl(l_counter).wip_entity_id;
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
--MP Delete Changes Start
BEGIN
select *
bulk collect into l_wsm_rsv_v_tbl
from wsm_reservations_v
where wip_entity_id = p_starting_jobs_tbl(l_counter).wip_entity_id;
--MP Delete Changes End
--MP Delete Changes
If l_wsm_rsv_v_tbl.count > 0 then --MP Delete Changes
For i in l_wsm_rsv_v_tbl.first .. l_wsm_rsv_v_tbl.last loop
l_rsv_old.reservation_id := l_wsm_rsv_v_tbl(i).reservation_id;
inv_reservation_pub.Delete_reservation (
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv_old
, p_serial_number => l_dummy_sn
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
end loop; --MP delete reservations loop
end if; --MP Delete Changes :End of check on l_wsm_rsv_v_tbl.count > 0
update wip_discrete_jobs
set net_quantity = p_resulting_jobs_tbl(l_rj_index).net_quantity
where wip_entity_id = p_resulting_jobs_tbl(l_rj_index).wip_entity_id;
WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate(
p_wip_entity_id => p_resulting_jobs_tbl(l_rj_index).wip_entity_id,
P_old_net_qty => l_rsvd_qty, --p_resulting_jobs_tbl(l_rj_index).net_quantity,
P_new_net_qty => p_resulting_jobs_tbl(l_rj_index).net_quantity,
P_inventory_item_id => p_resulting_jobs_tbl(l_rj_index).primary_item_id,
P_org_id => p_txn_header.organization_id,
P_status_type => p_resulting_jobs_tbl(l_rj_index).status_type,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
); --this is to handle the change in net qty if any.
l_msg_tokens.delete;
p_msg_text => 'WSM_RESERVATIONS_PVT.Modify_reservations_jobupdate failed:'||l_msg_data,
p_stmt_num => l_stmt_num ,
p_msg_tokens => l_msg_tokens,
p_fnd_msg_level => G_MSG_LVL_ERROR ,
p_fnd_log_level => G_LOG_LEVEL_ERROR ,
p_run_log_level => l_log_level
);
l_wsm_rsvn_tbl.delete;
--Insert the reservations for this job in the WSM_RESERVATIONS table.Before that check if the reserved qty against the job is more than what exactly the job has at completion.If that is the case,reduce reservations associated with the job.
l_rsvd_qty := check_reservation_quantity(p_wip_entity_id => p_wip_entity_id,
P_org_id => p_org_id,
P_inventory_item_id => p_inventory_item_id
) ;
select primary_uom_code
into l_mtl_rsv_rec.expected_quantity_uom
from mtl_system_items
where inventory_item_id =l_mtl_rsv_rec.inventory_item_id
and organization_id = l_mtl_rsv_rec.organization_id;
-- , delete_flag =>'N'
-- );
, p_delete_flag => 'N'
, p_sort_by_criteria => null
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
INSERT INTO wsm_reservations (
Wip_entity_id,
Reservation_id,
Demand_source_header_id,
Demand_source_line_id,
Reserved_qty)
(select wip_entity_id,
reservation_id,
demand_source_header_id,
demand_source_line_id,
primary_quantity
from wsm_reservations_v
where wip_entity_id= p_wip_entity_id
and organization_id = p_org_id
and inventory_item_id = p_inventory_item_id
);
select *
bulk collect into l_wsm_rsvn_tbl
from wsm_reservations
where wip_entity_id = p_wip_entity_id;
select status_type
into l_status_type
from wip_discrete_jobs
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
update wip_discrete_jobs
set status_type = 3
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
select primary_reservation_quantity
into l_reservation_quantity
from mtl_reservations
where reservation_id = l_wsm_rsvn_tbl(j).reservation_id;
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
-- status before it was updated to Released for trf the reservation.
update wip_discrete_jobs
set status_type = l_status_type
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id;
/*now delete the rows corresponding to this job in wsm_reservations*/
delete from wsm_reservations
where wip_entity_id = p_wip_entity_id;
Procedure modify_reservations_jobupdate (p_wip_entity_id IN NUMBER,
P_old_net_qty IN NUMBER ,
P_new_net_qty IN NUMBER,
P_inventory_item_id IN NUMBER,
P_org_id IN NUMBER,
P_status_type IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
is
l_rsv inv_reservation_global.mtl_reservation_rec_type;
l_module CONSTANT VARCHAR2(100) := 'wsm.plsql.WSM_RESERVATIONS_PVT.modify_reservations_jobupdate';
savepoint start_modify_rsv_jobupdate;
-- , delete_flag => 'N'
-- );
select primary_uom_code
into l_expected_quantity_uom --l_mtl_rsv_rec.expected_quantity_uom
from mtl_system_items
where inventory_item_id =l_mtl_rsv_rec.inventory_item_id
and organization_id = l_mtl_rsv_rec.organization_id;
, p_delete_flag => 'N'
, p_sort_by_criteria => null
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
-- For deleting reservations we need to use reduce reservation with p_delete_flag = 'Y'
l_mtl_rsv_rec.action := 0;
, p_delete_flag => 'Y'
, p_sort_by_criteria => null
);
-- Commenting call to delete_reservation API for bug 5347562.
/* inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv
, p_serial_number => l_dummy_sn
); */
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
-- Commenting call to delete_reservation API for bug 5347562.
/*
inv_reservation_pub.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, p_rsv_rec => l_rsv
, p_serial_number => l_dummy_sn
); */
-- For deleting reservations we need to use reduce reservation with p_delete_flag = 'Y'
l_mtl_rsv_rec.action := 0;
, p_delete_flag => 'Y'
, p_sort_by_criteria => null
);
WSM_log_PVT.update_errtbl(l_msg_index,l_msg_count);
l_msg_tokens.delete;
ROLLBACK TO start_modify_rsv_jobupdate;
ROLLBACK TO start_modify_rsv_jobupdate;
ROLLBACK TO start_modify_rsv_jobupdate;
Select sum (primary_quantity)
into l_rsvd_qty
from wsm_reservations_v
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and inventory_item_id = p_inventory_item_id;
select 1 into l_rsv_exists
from wsm_reservations_v
where wip_entity_id = p_wip_entity_id
and organization_id = p_org_id
and inventory_item_id = p_inventory_item_id
and rownum = 1;