Dear All,

I am trying to use the following procedure as a datasource for my crystal report. The query works as I expected but the problem is I can't figure out how to fetch back the data from those dummy tables - IFS_PR_DUMMY_TAB and IFS_PR_DUMMY2_TAB


CREATE OR REPLACE procedure dummy9_IFS_FR2_Sales (cdate IN date)

as

acontract customer_order.contract%type;
ashowroom customer_order.district_code%type;
aorderno customer_order.order_no%type;
amount number(10);

bcontract customer_order.contract%type;
bshowroom customer_order.district_code%type;
borderno customer_order.order_no%type;
bamount number(10);

CURSOR c2 IS
select contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Released') ) AND state IN ('Released') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
group by contract,district_code, date_entered ;

CURSOR c2 IS
select contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Reserved') ) AND state IN ('Reserved') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
group by contract,district_code, date_entered ;

begin

--For Released Orders

OPEN c1;

DELETE FROM IFS_PR_DUMMY_TAB;

loop
fetch c1 into acontract, ashowroom, aorderno, amount;
exit when c1%notfound;

Insert into IFS_PR_DUMMY_TAB
(DCONTRACT ,DSHOWROOM ,DORDERNO,DAMOUNT) values (acontract,ashowroom,aorderno,amount);

end loop;
close c1;

--For Reserved Orders

OPEN c2;

DELETE FROM IFS_PR_DUMMY2_TAB;

loop

fetch c2 into bcontract, bshowroom, borderno, bamount;
exit when c2%notfound;

Insert into IFS_PR_DUMMY2_TAB
(ECONTRACT ,ESHOWROOM ,EORDERNO,EAMOUNT) values (bcontract,bshowroom,borderno,bamount);

end loop;
close c2;

end;

Please advise.

Regards,
User