LOV with conditions

Paul Jorstad

Let's say I have a modified EMP table:

create table emp( 

  empno  number(4,0), 

  ename varchar2(10), 

  job varchar2(9), 

  partnerno number(4,0),

  constraint pk_emp primary key (empno), 

  constraint fk_emp foreign key (partnerno ) references emp(empno)

)

This is exposed in an interactive grid. As you see there is a foreign key to the table itself for the column partnerno, which is, sort of, the partner to the employee. When working with the employees in the interactive grid I want a LOV to select the partner:

- The LOV should not show the employee on the same row. (An employee can't be a partner of himself).

- The LOV should not show employees that already are registered as a partner (one employee can have one and only one partner).

- The LOV should show the partner name in the interactive grid (when not in edit mode for example)

Suggestions to solve this?

<span class="hljs-operator"><span class="hljs-keyword"></span><br/></span>

Rick-Pick

You can try this:

SELECT a.ename||' '||a.empno d, a.empno r

FROM emp a

WHERE a.empno != :EMPNO

AND NOT EXISTS (

  SELECT 1

  FROM emp b

  WHERE b.partnerno IS NOT NULL

  AND b.partnerno = a.empno

  )

UNION ALL

SELECT c.ename||' '||c.empno, c.empno

FROM emp c

WHERE c.empno = (

  SELECT d.partnerno

  FROM emp d

  WHERE d.empno=:EMPNO)

ORDER BY 1;

You have to define the Cascading LOV Parent Column as EMPNO.

The second part of the union only returns the partner of the current emp, if there is one. Without it you won't see the partner.

Of course it will only work for saved records. If you need it to work dynamically across records without the user saving each time, you will need to use a JavaScript DA to add each partnerno to a hidden page item, separating them with ":", then use INSTR to eliminate them in the above query - clearing the page item when the records are saved. I think it can be done, but whether it's worth it is up to you!

Paul Jorstad

Thanks for replying! :EMPNO will be the EMPNO of the current row then? Anything I need to do to pass that bindvariable to the LOV query? Not sure if I can pick it up.. But will try this!

M Tajuddin

Hi,

You can try this, however, I haven't tested the code though

SELECT ename, empno  FROM emp -- display the name of the empWHERE empno != :Px_EMP_NO -- matches the first conditionAND NOT EXISTS(SELECT 1 FROM emp WHERE partnerno != empno) -- exclude the emp who are assigned as partner

Ora_Learner890

may be this help, didn't tested

select b.ename partner_name, a.empno partner_no  from (      select empno from emp where empno <> :EMPNO -- first Select ALL except in current row      minus      select partnerno from emp  -- exclude ALL who are already assigned as Partner ) a, emp bwhere a.empno = b.empno

Paul Jorstad

Thanks, it worked out! I'm not sure why we need to define it as a cascading LOV just to be able to use the values of the row? I tried to make the report refresh after a change in the partner column but didn't make it. If I created a dynamic action for the partner column, the value from the LOV was not saved, but report refreshed. Any idea how to set the value in the cell from LOV and then, save the interavtive grid? And the refresh it? IE: 1: Set the value from LOV, 2: Save the grid, 3: Refresh the grid. Would be nice :-)

Rick-Pick

I'm not sure I would want to take control of the save away from the user, but I suppose you want to do it to avoid the issue I mentioned that the partner just added will not disappear from the LOV unless you do save. You can invoke a save on the IG with:

   apex.region("region-staticid").call("getActions").invoke("save");

But trying to do this within the grid itself as you're suggesting will I think cause a big headache. Much easier to just ask the users to save after each record if they change the partner field.

Incidentally, I think you need the cascading LOV to make it dynamic (and also to ensure that the EMPNO is saved in session state).