各位,这算是一个比较复杂的问题,想了很久,特地贴出来,采用自定义函数来实现,欢迎与我交流, QQ: 312076667

create or replace function BACKPRIC(item in number,vendorid in number,dateto in date)
  return number
  as
  v_sal po_lines_all.unit_price%type;
begin
   select a.unit_price into v_sal
  from (select poh.vendor_id,
               poh.segment1,
               pol.unit_price,
               poh.creation_date
          from po_headers_all poh, po_lines_all pol
         where poh.po_header_id = pol.po_header_id
           and poh.type_lookup_code != 'QUOTATION'
           and ((pol.closed_code is null or pol.closed_code != 'FINALLY CLOSED'))
           and pol.quantity<>0
           and pol.item_id = item
           and poh.vendor_id = vendorid
           and poh.creation_date <dateto
         order by poh.creation_date desc) a
 where rownum = 1;
   return v_sal;
end backpric;