手工RUN:Import Delivery Lines後可能造成WSH_DELIVERY_DETAILS中資料重復

查核是否有重復資料,我用的SQL是

select a.*
from (
select l.org_id,h.order_number,h.header_id,
l.line_id,l.ordered_quantity,
(select sum(d.REQUESTED_QUANTITY)
from wsh_delivery_details d
where d.source_line_id=l.line_id
and d.source_header_id=l.header_id
and d.source_code='OE'
) d_qty
-- into &orgid,&order_no,&headerid,&lineid,&ordqty,&wshqty
from oe_order_lines_all l,oe_order_headers_all h
where l.org_id=:ORG_ID
and h.header_id=l.header_id
and l.open_flag='Y'
) a
where a.ordered_quantity<>d_qty

--我是用QTY來比較的

--依上面SQL出來如有記錄,則用LINE_ID作條件用下面的SQL檢視.
select l.line_id,l.ordered_item,l.ordered_quantity,l.CANCELLED_QUANTITY, d.REQUESTED_QUANTITY,
d.released_status,d.CREATED_BY,d.CREATION_DATE,
d.*
from wsh_delivery_details d,oe_order_lines_all l
where l.line_id in (6738108,6738118)
and l.line_id=d.source_line_id
and d.source_code='OE'

--有重復資料時,一般d.released_status會不同一個R,一個N.我都是將N的作CANCELLED(SQL如下)
update wsh_delivery_details
set SRC_REQUESTED_QUANTITY=0,
cancelled_quantity=SRC_REQUESTED_QUANTITY,
requested_quantity=0,
RELEASED_STATUS='D',
DATE_SCHEDULED=null
where delivery_detail_id in (6765925,6766661) --=6597192

commit --最後提交

[@more@]

select a.*
from (
select l.org_id,h.order_number,h.header_id,
l.line_id,l.ordered_quantity,
(select sum(d.REQUESTED_QUANTITY)
from wsh_delivery_details d
where d.source_line_id=l.line_id
and d.source_header_id=l.header_id
and d.source_code='OE'
) d_qty
-- into &orgid,&order_no,&headerid,&lineid,&ordqty,&wshqty
from oe_order_lines_all l,oe_order_headers_all h
where l.org_id=801
and h.header_id=l.header_id
and l.open_flag='Y'
) a
where a.ordered_quantity<>d_qty

select l.line_id,l.ordered_item,l.ordered_quantity,l.CANCELLED_QUANTITY, d.REQUESTED_QUANTITY,
d.released_status,d.CREATED_BY,d.CREATION_DATE,
d.*
from wsh_delivery_details d,oe_order_lines_all l
where l.line_id in (6738108,6738118)
and l.line_id=d.source_line_id
and d.source_code='OE'

update wsh_delivery_details
set SRC_REQUESTED_QUANTITY=0,
cancelled_quantity=SRC_REQUESTED_QUANTITY,
requested_quantity=0,
RELEASED_STATUS='D',
DATE_SCHEDULED=null
where delivery_detail_id in (6765925,6766661) --=6597192

commit