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


Edited by signil - 26 Nov 2009 at 2:48am