2018年6月26日 星期二
CANCEL RMA
RMA： 104030029
select f.user_name,f.description ,h.created_by,h.creation_date, h.header_id ,h.order_number,h.flow_status_code, l.ordered_quantity
from oe_order_headers_all h,oe_order_lines_all l,fnd_user f
where h.header_id = l.header_id
and h.created_by = f.user_id
and h.order_number in ( '104030029')
-- Update Header
update oe_order_headers_all
set flow_status_code ='CANCELLED'
where header_id in (select distinct h.header_id
from oe_order_headers_all h,oe_order_lines_all l
where h.header_id = l.header_id
and h.order_number in ( '104030029'))
-- Update lines
declare
cursor c1 is
select ordered_quantity,line_id
from oe_order_lines_all
where header_id in (select distinct h.header_id
from oe_order_headers_all h,oe_order_lines_all l
where h.header_id = l.header_id
and h.order_number in ( '104030029'));
begin
for c in c1 loop
update oe_order_lines_all
set pricing_quantity = 0, --10000,
cancelled_quantity =c.ordered_quantity, --0
ordered_quantity =0 , --10000,
FULFILLED_QUANTITY= null,--10000
cancelled_flag ='Y',-- N
INVOICED_QUANTITY = null ,-- -10000
flow_status_code ='CANCELLED', --'CLOSED'
FULFILLMENT_DATE = null,-- 2013/12/30 下午 01:03:27
lock_control = 5,--6
ACTUAL_FULFILLMENT_DATE= null, --2013/12/30 下午 12:41:14
INVOICE_INTERFACE_STATUS_CODE =null
where line_id =c.line_id;
dbms_output.put_line('line_id :'|| c.line_id ||' order Qty'||c.ordered_quantity);
end loop;
end;
2018年5月23日 星期三
oracle db session lock
--判斷是否有Session Lock，觀察seconds_in_wait秒數是否過長
select
(select count(1)
from v$session a2
where a2.blocking_session is not NULL
and a2.sid=a1.blocking_session
) r_count,
a1.blocking_session,
(
select a2.serial#
from v$session a2
where a2.sid= a1.blocking_session
) blocking_serial,
a1.sid,
a1.serial#,
a1.wait_class,
a1.module,
a1.seconds_in_wait,
a1.action
from v$session a1
where a1.blocking_session is not NULL
And a1. blocking_session<>1693
order by 1;
--確認Lock Session是不是在Inactive狀態
--WAY2
select sid,serial#,module,action,status,event,seconds_in_wait,logon_time,osuser,process,SYSDATE - (last_call_et / 86400) "Last_Call"
from v$session
where sid=2851;
--刪除Lock Session
alter system kill session 'SID,SERIAL#';
訂閱：
文章 (Atom)