SELECT oh.order_number
, OOLA.line_number
|| '.'
|| OOLA.shipment_number line_number
, decode(oola.line_category_code,'RETURN',-oola.Ordered_Quantity,OOLA.ORDERED_QUANTITY)
FROM OE_ORDER_LINES_ALL OOLA,
oe_order_headers_all OH,--ADD BY RAYMENG 2008-06-26
HR_ORGANIZATION_UNITS HOU
WHERE HOU.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND OH.HEADER_ID = OOLA.HEADER_ID
AND ((OH.ORDER_TYPE_ID NOT IN (1015,1026,1027,1108)) --不取订单类型为 %进口%和%内部%和%佣金%的订单
--OR (OH.ORDER_TYPE_ID IN (1015,1026,1027) AND OOLA.flow_status_code <> 'AWAITING_SHIPPING') --取订单类型为 %进口%和%内部%的订单，但是不取等待发运状态的订单行
)
AND OOLA.ORDERED_QUANTITY - NVL(OOLA.SHIPPED_QUANTITY, 0) > 0
--如果是佣金或订单状态为'CLOSED','ENTERED','CANCELLED'的均不统计
and 1= case when (OH.ORDER_TYPE_ID in (1128,1068,1108,1109,1067))
or
(OOLA.flow_status_code IN ('CLOSED','ENTERED','CANCELLED') )
then 0 else 1 end
AND NOT EXISTS(
select 'X'
from OE_ORDER_HOLDS_ALL oh
left join OE_HOLD_SOURCES_ALL hs
on oh.hold_source_id=hs.hold_source_id
where oh.header_id = oola.header_id
AND oh.line_id = oola.line_id
and 1= case when hs.HOLD_ID=1 then 0 else 1 end --信用控制类型的不查询出来
and oh.released_flag= 'N'
)
--and oola.line_category_code='RETURN'
