create or replace
procedure jobExpDel(
cursor_in_warehouse out sys_refcursor,
cursor_in_wh_cargo out sys_refcursor,
cursor_in_wh_cargo_detail out sys_refcursor,
cursor_out_warehouse out sys_refcursor,
cursor_out_wh_cargo out sys_refcursor,
cursor_out_wh_cargo_detail out sys_refcursor,
cursor_wh_take_stock out sys_refcursor,
cursor_wh_take_stock_record out sys_refcursor,
i_months Int ---月份,删除多少月前的数据
) is
D_BEFOR DATE;
I_MONTHS_R INT;
begin
if i_months>0 then
I_MONTHS_R:=-i_months;
ELSE
I_MONTHS_R:=i_months;
end if;
D_BEFOR:= add_months(sysdate,I_MONTHS_R);
-- 把out_wh_cargo_detail要删除的数据插入到临时表-------------
insert into temp_out_wh_cargo_detail select t0.* from out_wh_cargo_detail t0
join out_wh_cargo t1 on t0.cargo_id=t1.cargo_id
join out_warehouse t2 on t1.out_warehouse_id=t2.out_warehouse_id
join bac_cargoinfo c on c.cargoinfo_id=t1.cargoinfo_id
join bac_cargo_category cc on cc.cc_id=c.cc_id
join bac_cargo_category cc2 on cc.cc_p_id=cc2.cc_id
where(t2.out_warehouse_type = 'OUT_DUMPING'--报废出库的
or
--易耗品全部出库
(cc2.cc_code='G'and not exists(
select 1 from wh_inventory inventory where (inventory.cargo_type='IN' or (inventory.cargo_type='OUT' and inventory.modify_time>D_BEFOR))
and t0.cargo_no=inventory.cargo_no
))
)
and t2.modify_time <= D_BEFOR;
delete from out_wh_cargo_detail t0 where exists (select 1 from temp_out_wh_cargo_detail t1 where t1.detail_id=t0.detail_id);
----- end out_wh_cargo_detail-----------
----------把out_wh_cargo要删除的数据插入到临时表-------------
insert into temp_out_wh_cargo select * from out_wh_cargo t where t.modify_time<=D_BEFOR and not exists(
select 1 from out_wh_cargo_detail t0 where t0.cargo_id=t.cargo_id
);
delete from out_wh_cargo t0 where exists(select 1 from temp_out_wh_cargo t1 where t1.cargo_id=t0.cargo_id);
----------end out_wh_cargo----------------------
----------start out_warehouse-------------------
insert into temp_out_warehouse select * from out_warehouse t where t.modify_time<=D_BEFOR and not exists(
select 1 from out_wh_cargo t0 where t0.out_warehouse_id= t.out_warehouse_id
);
delete from out_warehouse t0 where exists(select 1 from temp_out_warehouse t1 where t1.out_warehouse_id=t0.out_warehouse_id);
----------end out_warehouse--------------------
---------- statr in_wh_cargo_detail-------------------
insert into temp_in_wh_cargo_detail select * from in_wh_cargo_detail t where exists(
select 1 from temp_out_wh_cargo_detail t0 where t0.cargo_no=t.cargo_no
);
delete from in_wh_cargo_detail t0 where exists(select 1 from temp_in_wh_cargo_detail t1 where t1.detail_id=t0.detail_id);
---------- end in_wh_cargo_detail -------------------
----------start in_wh_cargo -------------
insert into temp_in_wh_cargo select * from in_wh_cargo t where t.modify_time<=D_BEFOR and not exists(
select 1 from in_wh_cargo_detail t0 where t0.cargo_id=t.cargo_id
);
delete from in_wh_cargo t0 where exists(select 1 from temp_in_wh_cargo t1 where t1.cargo_id=t0.cargo_id);
----------end in_wh_cargo----------------------
----------start in_warehouse-------------------
insert into temp_in_warehouse select * from in_warehouse t where t.modify_time<=D_BEFOR and not exists(
select 1 from in_wh_cargo t0 where t0.in_warehouse_id= t.in_warehouse_id
);
delete from in_warehouse t0 where exists(select 1 from temp_in_warehouse t1 where t1.in_warehouse_id=t0.in_warehouse_id);
----------end out_warehouse--------------------
------------start wh_take_stock_record ------------
insert into temp_wh_take_stock_record select * from wh_take_stock_record t where exists(
select 1 from temp_out_wh_cargo_detail t0 where t0.cargo_no=t.cargo_no
);
delete from wh_take_stock_record t0 where exists(select 1 from temp_wh_take_stock_record t1 where t0.record_id=t1.record_id);
------------end wh_take_stock_record ------------
------------start wh_take_stock-------------------
insert into temp_wh_take_stock select * from wh_take_stock t where t.modify_time<=D_BEFOR and not exists(
select 1 from temp_wh_take_stock_record t0 where t.take_stock_id=t0.take_stock_id
);
delete from wh_take_stock t0 where exists(select 1 from temp_wh_take_stock t1 where t0.take_stock_id=t1.take_stock_id);
------------end wh_take_stock
------------wh_inventory------------
delete from wh_inventory t0 where exists(select 1 from temp_out_wh_cargo_detail t1 where t0.cargo_no=t1.cargo_no);
open cursor_in_warehouse for
select * from temp_in_warehouse;
open cursor_in_wh_cargo for
select * from temp_in_wh_cargo;
open cursor_in_wh_cargo_detail for
select * from temp_in_wh_cargo_detail;
open cursor_out_warehouse for
select * from temp_out_warehouse;
open cursor_out_wh_cargo for
select * from temp_out_wh_cargo;
open cursor_out_wh_cargo_detail for
select * from temp_out_wh_cargo_detail;
open cursor_wh_take_stock for
select * from temp_wh_take_stock;
open cursor_wh_take_stock_record for
select * from temp_wh_take_stock_record;
end jobExpDel;
procedure jobExpDel(
cursor_in_warehouse out sys_refcursor,
cursor_in_wh_cargo out sys_refcursor,
cursor_in_wh_cargo_detail out sys_refcursor,
cursor_out_warehouse out sys_refcursor,
cursor_out_wh_cargo out sys_refcursor,
cursor_out_wh_cargo_detail out sys_refcursor,
cursor_wh_take_stock out sys_refcursor,
cursor_wh_take_stock_record out sys_refcursor,
i_months Int ---月份,删除多少月前的数据
) is
D_BEFOR DATE;
I_MONTHS_R INT;
begin
if i_months>0 then
I_MONTHS_R:=-i_months;
ELSE
I_MONTHS_R:=i_months;
end if;
D_BEFOR:= add_months(sysdate,I_MONTHS_R);
-- 把out_wh_cargo_detail要删除的数据插入到临时表-------------
insert into temp_out_wh_cargo_detail select t0.* from out_wh_cargo_detail t0
join out_wh_cargo t1 on t0.cargo_id=t1.cargo_id
join out_warehouse t2 on t1.out_warehouse_id=t2.out_warehouse_id
join bac_cargoinfo c on c.cargoinfo_id=t1.cargoinfo_id
join bac_cargo_category cc on cc.cc_id=c.cc_id
join bac_cargo_category cc2 on cc.cc_p_id=cc2.cc_id
where(t2.out_warehouse_type = 'OUT_DUMPING'--报废出库的
or
--易耗品全部出库
(cc2.cc_code='G'and not exists(
select 1 from wh_inventory inventory where (inventory.cargo_type='IN' or (inventory.cargo_type='OUT' and inventory.modify_time>D_BEFOR))
and t0.cargo_no=inventory.cargo_no
))
)
and t2.modify_time <= D_BEFOR;
delete from out_wh_cargo_detail t0 where exists (select 1 from temp_out_wh_cargo_detail t1 where t1.detail_id=t0.detail_id);
----- end out_wh_cargo_detail-----------
----------把out_wh_cargo要删除的数据插入到临时表-------------
insert into temp_out_wh_cargo select * from out_wh_cargo t where t.modify_time<=D_BEFOR and not exists(
select 1 from out_wh_cargo_detail t0 where t0.cargo_id=t.cargo_id
);
delete from out_wh_cargo t0 where exists(select 1 from temp_out_wh_cargo t1 where t1.cargo_id=t0.cargo_id);
----------end out_wh_cargo----------------------
----------start out_warehouse-------------------
insert into temp_out_warehouse select * from out_warehouse t where t.modify_time<=D_BEFOR and not exists(
select 1 from out_wh_cargo t0 where t0.out_warehouse_id= t.out_warehouse_id
);
delete from out_warehouse t0 where exists(select 1 from temp_out_warehouse t1 where t1.out_warehouse_id=t0.out_warehouse_id);
----------end out_warehouse--------------------
---------- statr in_wh_cargo_detail-------------------
insert into temp_in_wh_cargo_detail select * from in_wh_cargo_detail t where exists(
select 1 from temp_out_wh_cargo_detail t0 where t0.cargo_no=t.cargo_no
);
delete from in_wh_cargo_detail t0 where exists(select 1 from temp_in_wh_cargo_detail t1 where t1.detail_id=t0.detail_id);
---------- end in_wh_cargo_detail -------------------
----------start in_wh_cargo -------------
insert into temp_in_wh_cargo select * from in_wh_cargo t where t.modify_time<=D_BEFOR and not exists(
select 1 from in_wh_cargo_detail t0 where t0.cargo_id=t.cargo_id
);
delete from in_wh_cargo t0 where exists(select 1 from temp_in_wh_cargo t1 where t1.cargo_id=t0.cargo_id);
----------end in_wh_cargo----------------------
----------start in_warehouse-------------------
insert into temp_in_warehouse select * from in_warehouse t where t.modify_time<=D_BEFOR and not exists(
select 1 from in_wh_cargo t0 where t0.in_warehouse_id= t.in_warehouse_id
);
delete from in_warehouse t0 where exists(select 1 from temp_in_warehouse t1 where t1.in_warehouse_id=t0.in_warehouse_id);
----------end out_warehouse--------------------
------------start wh_take_stock_record ------------
insert into temp_wh_take_stock_record select * from wh_take_stock_record t where exists(
select 1 from temp_out_wh_cargo_detail t0 where t0.cargo_no=t.cargo_no
);
delete from wh_take_stock_record t0 where exists(select 1 from temp_wh_take_stock_record t1 where t0.record_id=t1.record_id);
------------end wh_take_stock_record ------------
------------start wh_take_stock-------------------
insert into temp_wh_take_stock select * from wh_take_stock t where t.modify_time<=D_BEFOR and not exists(
select 1 from temp_wh_take_stock_record t0 where t.take_stock_id=t0.take_stock_id
);
delete from wh_take_stock t0 where exists(select 1 from temp_wh_take_stock t1 where t0.take_stock_id=t1.take_stock_id);
------------end wh_take_stock
------------wh_inventory------------
delete from wh_inventory t0 where exists(select 1 from temp_out_wh_cargo_detail t1 where t0.cargo_no=t1.cargo_no);
open cursor_in_warehouse for
select * from temp_in_warehouse;
open cursor_in_wh_cargo for
select * from temp_in_wh_cargo;
open cursor_in_wh_cargo_detail for
select * from temp_in_wh_cargo_detail;
open cursor_out_warehouse for
select * from temp_out_warehouse;
open cursor_out_wh_cargo for
select * from temp_out_wh_cargo;
open cursor_out_wh_cargo_detail for
select * from temp_out_wh_cargo_detail;
open cursor_wh_take_stock for
select * from temp_wh_take_stock;
open cursor_wh_take_stock_record for
select * from temp_wh_take_stock_record;
end jobExpDel;
owms_root。
执行 PL/SQL: ALTER SESSION SET PLSQL_DEBUG=TRUE
执行 PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( '192.168.12.85', '61357' )
ORA-01031: 权限不足
ORA-06512: 在 "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: 在 line 1
此会话需要 DEBUG CONNECT SESSION 和 DEBUG ANY PROCEDURE 用户权限。
进程已退出。