SQL-WSH - update OP delivery from oracle

query - WSH - update OP delivery

/*  this is the script to identify open deliveries but all lines are shipped
see tar SR 3-7848981781
*/

select distinct wdd.source_header_number, wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
and trunc(wdd.creation_date) > sysdate -365
and wda.delivery_id = wnd.delivery_id
and wdd.released_status = 'C'
and wnd.status_code not in ('IT', 'CL')
order by 1;


3) If you want to just close the reported delivery 459098, then you can run the following:
UPDATE wsh_new_deliveries
SET status_code = 'CL',
last_update_date = SYSDATE,
last_updated_by = -1
WHERE delivery_id = 459098
and status_code = 'OP';


/*  this is the script to set delivery status to closed
see tar SR 3-7848981781
*/

UPDATE wsh_new_deliveries
SET    status_code      = 'CL',
       last_update_date = SYSDATE,
       last_updated_by   = -1
WHERE delivery_id IN ( select distinct wnd.delivery_id
from wsh_delivery_details wdd,wsh_delivery_assignments
wda,wsh_new_deliveries wnd
where wdd.source_code = 'OE'
and wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id  = wnd.delivery_id
and trunc(wdd.creation_date) > sysdate -365
and wdd.released_status = 'C'
and wnd.status_code = 'OP');

wsh_picking_batches