Hello,
I can't find any help in the whole internet which can resolve my problem, so before i give up my attempts , i try to ask some help from you.
So..
I have 3 tables CUSTOMER (cid, cname,sid), WAREHOUSE(wid,wname),SALESPERSON(sid,sname,wid)
These can act as SHIPPER and RECIPIENT too.
I have made a view: ACTOR( CREATE OR REPLACE FORCE VIEW "ACTOR" ("ID", "NAME", "TYPE") AS SELECT wid,widname, 1 FROM WAREHOUSE UNION SELECT sid,sidname, 2 FROM SALESPERSON UNION SELECT cid,cidname ,3 FROM CUSTOMER
My goal is there are things what these actors can excange. customer can only excange things with its own salesperson, salespersom can only excange with its own customers and warehouse, warehouse can only excange with its salespersons. So i create a page where a LOV item depends on another LOV item and when the first changes the values of second change too..
Finally I created an apex page with 2 LOV items.
The first one named :SHIPPER has a sql query source : select name,id from ACTOR
I want that the values of the item named :RECIPIENT depends on the value of :SHIPPER
So I set to recipient that type is PL/SQL Function body returning sql query and Cascading LOV Parent Item(s) is :SHIPPER, and Items to Submit is :SHIPPER too.
The function:
declare
q varchar2(4000);
shipper varchar(20) := :SHIPPER;
actortype number;
begin
SELECT type INTO actortype FROM actor where id = shipper;
case type
when '1' then
q:=' select sname,sid ';
q:=q||' from SALESPERSON ';
q:=q||' where wid=shipper';
when '2' then
q:=' select wname,wid ';
q:=q||' from warehouse ';
q:=q||' where wid = (select wid from salesperson where sid=shipper)';
q:=q||'UNION';
q:=q||' select cname,cid ';
q:=q||' from customer ';
q:=q||' where sid=(select sid from customer where sid=shipper)';
else
q:=' select sname, sid ';
q:=q||' from salesperson ';
q:=q||' where sid = (select sid from customer where cid= shipper)';
end case;
return q;
end;
SO It's not working.... I tried a lots of way but always get an error.for example:
If declare shipper varchar(20) := :SHIPPER; I get error no data..
If I try to set the variable shipper anywhere to variable q I get error invalid identifier..
etc....
So how could i resolve this??
Thanks so much