select distinct t.po_header_id,t.po_line_id,
sum(decode(t.transaction_type,'RECEIVE',t.quantity,'RETURN TO VENDOR',-t.quantity,0))over(partition by t.po_header_id,t.po_line_id order by null) rcv_sum_qty,
sum(decode(t.transaction_type,'DELIVER',t.quantity,'RETURN TO RECEIVING',-t.quantity,0)) over(partition by t.po_header_id,t.po_line_id order by null)dev_sum_qty
from po.rcv_transactions t
start with (t.organization_id = 32
--and t.transaction_date between to_date('2013-01-01','yyyy-mm-dd') and to_date('2013-01-31','yyyy-mm-dd')
AND t.po_header_id in (
select pb.po_header_id
from po.po_vendors pa,
po.po_headers_all pb
where pb.org_id = 31
and pa.vendor_id = pb.vendor_id
and pa.vendor_name like '%凯凯%'
)
and t.parent_transaction_id = -1)
connect by prior t.transaction_id =t.parent_transaction_id
这样写就没问题
select t.po_header_id,t.po_line_id,
sum(decode(t.transaction_type,'RECEIVE',t.quantity,'RETURN TO VENDOR',-t.quantity,0)) rcv_sum_qty,
sum(decode(t.transaction_type,'DELIVER',t.quantity,'RETURN TO RECEIVING',-t.quantity,0))dev_sum_qty
from po.rcv_transactions t
start with (t.organization_id = 32
--and t.transaction_date between to_date('2013-01-01','yyyy-mm-dd') and to_date('2013-01-31','yyyy-mm-dd')
AND (t.transaction_date BETWEEN TO_DATE('${接收时间从}','YYYY-MM-DD') AND TO_DATE('${到}','YYYY-MM-DD'))
and t.parent_transaction_id = -1)
connect by prior t.transaction_id =t.parent_transaction_id
group by t.po_header_id,t.po_line_id
这样也没问题，觉得很奇怪啊
|
