OM裡±£Áô記錄備·ÝSQL

 First:
create table gobo.gobo_om_reservations_2008b as
select * from gobo_om_reservations
where to_char(CREATION_DATE,'yyyy')<'2008'
delete from gobo_om_reservations
where to_char(CREATION_DATE,'yyyy')<'2008'
commit
After 2009Year:
create table gobo.gobo_om_reservations_????b as --????=to_char(sysdate-400,'yyyy')
select * from gobo.gobo_om_reservations_2008b
insert into gobo.gobo_om_reservations_????b
select * from gobo_om_reservations
where to_char(CREATION_DATE,'yyyy')<'????'
delete from gobo_om_reservations
where to_char(CREATION_DATE,'yyyy')<'????'
commit
ϵ統TABLE£ºmtl_reservations
µÄTRRIGERÈçÏ£º
--OTCµÄ訂單ÔÚOM發·Å¹¤單時£¬會×Ô動±£Áô¡£
--¼´°Ñ訂單與¹¤單聯ϵÆð來£¬¿É當訂單銷貨áᣬ
--»ò¹¤單Í깤áᣬ該關聯¼´Ïûʧ
--為Á˱£³Ö聯ϵ啟ÓÃ備·ÝµÄ·½·¨¡£
CREATE OR REPLACE TRIGGER gobo_om_reservations_in
after insert on mtl_reservations
for each row
begin
--舊µÄ·½Ê½備·Ýµ½gobo_om_reservations
insert into gobo_om_reservations
values( :new.SUPPLY_SOURCE_HEADER_ID,
:new.ORGANIZATION_ID,
:new.INVENTORY_ITEM_ID,
:new.DEMAND_SOURCE_TYPE_ID,
:new.DEMAND_SOURCE_HEADER_ID,
:new.DEMAND_SOURCE_LINE_ID,
:new.RESERVATION_QUANTITY,
:new.SUPPLY_SOURCE_TYPE_ID,
:new.CREATION_DATE,
:new.LAST_UPDATE_DATE );
--еķ½Ê½£¬°ÑWIP_JOD_ID寫µ½訂單LINE裡
--為ÁËÈËÁô舊ÉÏÃæµÄ·½Ê½£¬¹Ê²»È¡ÏûÉÏÃæµÄ·½·¨¡£
update oe_order_lines_all
set attribute19=:new.supply_source_header_id
where line_id= :new.demand_source_line_id
-- and header_id=:new.demand_source_header_id
and attribute19 is null;
end;