查詢刪除同訂單同一LINE NUMBER內有不同SHIP FORM ORG ID的處理

最近發現部分訂單分批銷貨後SHIP FROM ORG ID會變,如原為CS4,SPLIT後就變為CS1或CS2了.

這是之前寫的一個TRIGGER造成的,現已修正(增加
AND :NEW.shipment_number=1 then
).

CREATE OR REPLACE TRIGGER APPS.GOBO_OE_ORDER_LINES_ALL_T2
BEFORE
INSERT
ON ONT.OE_ORDER_LINES_ALL
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE

v_status varchar2(40);
v_ship_from_org_id number;

BEGIN
IF :NEW.ORG_ID = 14 OR :NEW.ORG_ID = 57 THEN

/* Order Organizer Product Status Visibility */
SELECT inventory_item_status_code
INTO v_status
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id=:new.inventory_item_id
AND organization_id=:new.ship_from_org_id;

:NEW.CUST_MODEL_SERIAL_NUMBER := v_status;

END IF;

--longson.long add 2010.01.14 online key so
if :new.org_id = 801 and :new.order_source_id=0
AND :NEW.shipment_number=1 then

begin

SELECT ORGANIZATION_ID
INTO v_ship_from_org_id
FROM (
SELECT organization_id,item_type
-- INTO v_ship_from_org_id
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id=:new.inventory_item_id
-- segment1='B4801101NP-INJ1'
AND organization_id in (802,803,804,805,806,821,841)
and msib.inventory_item_status_code='Active'
and msib.inventory_item_flag='Y'
and msib.stock_enabled_flag='Y'
-- and trunc(nvl(end_date_active,sysdate))
-- and rownum=1
order by decode(item_type,'AI','1','2')
)
WHERE rownum=1
;

:new.ship_from_org_id := nvl(v_ship_from_org_id,801);
exception
when others then
null;
end;

end if;
--longson.long add 2010.01.14

END GOBO_OE_ORDER_LINES_ALL_T2;
/

[@more@]

create table a01a as
select header_id,line_number,count(ship_from_org_id) from_org
from
(
select distinct header_id,line_number,ship_from_org_id
from oe_order_lines_all
where org_id=801
)
group by header_id,line_number
having count(ship_from_org_id)>1

select h.order_number,l.line_id,l.line_number||'.'||l.shipment_number line_no,
l.ship_from_org_id,l.flow_status_code
from a01a a,oe_order_headers_all h,oe_order_lines_all l
where h.header_id=l.header_id
and l.header_id=a.header_id
and l.line_number=a.line_number
order by h.order_number,l.line_number,l.shipment_number

據此SQL結果查未結案的訂單,逐筆修正後再將臨時TABLE作DROP.

drop table a01a