How can I pass Recordsets as input parameter to a Function / Stored Proc ?

Hi Gurus,
I want to create a function that receive recordsets as input parameter also returns recordsets as output.

I have a requirement to do stock taking for all order items of one Order number in one single query execution (to avoid row by row basis).
I want to WRAP the query into a Function / Stored Proc so that it can be RE-USED by other transaction that need stock taking also.

My question is : how to pass the recordsets as input parameter to that function / SP ?
(because I could have 75 rows item in one order number)

Below is the (simplified) DDL and the query : create table stocks (Product char(4), Warehouse char(5), expireddate date, qty_available number)

insert into stocks values('P001', 'WH001', '01-dec-2006', 30)
insert into stocks values('P001', 'WH002', '01-dec-2006', 50)
insert into stocks values('P001', 'WH002', '01-jan-2007', 50 )

CREATE TABLE Order_Detail (PRODUCT_ORD CHAR(4), QTY_ORD number, Priority_WH CHAR(5) )
INSERT INTO Order_Detail VALUES ('P001', 75, 'WH003') // previously 'WH002'
INSERT INTO Order_Detail VALUES ('P002', 45, 'WH002') INSERT INTO Order_Detail VALUES ('P003', 55, NULL)

The query for stock taking :
select product,warehouse,expireddate, least(qty_available-(sm - qty_ord),qty_available) qty
from ( select product,warehouse,expireddate,qty_available,qty_ord, sum(qty_available) over(partition by product order by s.product,expireddate,decode(warehouse,priority_wh,0,1),warehouse ) sm
from stocks s,order_detail o
where s.product = o.product_ord order by s.product,expireddate,decode(warehouse,priority_wh,0,1) ) where (sm - qty_ord) < qty_available

Thank you very much,
xtanto Received on Tue Feb 13 2007 - 05:53:54 CST