--建一備份TABLE記錄保留記錄
CREATE TABLE GOBO_OM_RESERVATIONS
(
SUPPLY_SOURCE_HEADER_ID NUMBER,
ORGANIZATION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
DEMAND_SOURCE_TYPE_ID NUMBER,
DEMAND_SOURCE_HEADER_ID NUMBER,
DEMAND_SOURCE_LINE_ID NUMBER,
RESERVATION_QUANTITY NUMBER,
SUPPLY_SOURCE_TYPE_ID NUMBER,
CREATION_DATE DATE,
LAST_UPDATE_DATE DATE
)

CREATE OR REPLACE TRIGGER APPS.gobo_om_reservations_in
after insert on mtl_reservations
for each row
begin
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 );
if :new.organization_id in (801,802,803,804,805,806,821,625,628,862,98)
and :new.SUPPLY_SOURCE_TYPE_ID =13 then
-- SUPPLY_SOURCE_TYPE_ID 5 WIP 13 INV
--PROD CN 保留日期:ATTRIBUTE7

--工單ID: attribute19

-- 累加保留數量 ATTRIBUTE8

update oe_order_lines_all
set attribute7=to_char(sysdate,'yyyymmdd'),
attribute8=NVL(ATTRIBUTE8,0)+:new.reservation_quantity,
attribute19=:new.supply_source_header_id
where line_id= :new.demand_source_line_id
and attribute19 is null;
else
update oe_order_lines_all
set attribute19=:new.supply_source_header_id
where line_id= :new.demand_source_line_id
and attribute19 is null;
end if;
end;
/

[@more@]