You could use a Read-only Custom Field with a select statement. I had used the below query for the select statement and you can change the conditions according to your requirements.

select case when :ENG_CHG_LEVEL='1' then 'A Part' else 'B Bart' end from part_revision

 

And the result was like below.

 

Hope this is what you were looking for.

AVHELK,

Odd that IFS 'knows’ for which part the correct value of eng_chg_level is to be selected. 

I had a similar idea in mind, but used the following as select statement in the Implementation Type screen:

select case :PART_REV
when 'R01' then 'First Revision'
when 'R02' then 'Second one'
when 'R03' then 'Yet another revision'
when 'R04' then 'Fourth Revision'
else 'High Revision number'
end case
from eng_part_revision where part_no = :PART_NO and part_rev = :PART_REV

where the passed parameters are: v.part_no,v.part_rev

Steve

 select case :PART_REV
when 'R01' then 'First Revision'
when 'R02' then 'Second one'
when 'R03' then 'Yet another revision'
when 'R04' then 'Fourth Revision'
else 'High Revision number'
end case
from eng_part_revision where part_no = :PART_NO and part_rev = :PART_REV

Hi Steve,

Thanks for sharing your thoughts. 
I just wanted to provide an example of how to handle this type of scenario. As you mentioned, according to the requirements, you may need to add the relevant parameters to the select statement. 

Thanks,
Avindu

Hi,

Thanks for all your answers. 

OK for a read only custom field but what’s about if my field is a persistant one and i want to initialize it. I can only manage PL/SQL Expression with no parameter.

How to do this ? 

Thanks,

Eric

Eric,

I’ve no idea where you got that screen shot from, but can’t the Context Substitution Variable of help?

Steve

Hi Steve,

Previous screen shot comes from ‘no default vallue’ click link :

“Context Substitution Variable” doesn’t help

So I’m still waiting for a good answer ;) 

Eric

Eric,

On what should the default be based? The entry of new data is not available, I'm not technical enough to understand what is going on in the GUI layer, but what you enter is always to late to be picked up by a trigger to form a default.

Hope you understand what I'm getting at.

Steve

Have run into the same query myself, and I think how you have implemented is probably the safest and maybe the only way to get it working. Maybe add the procedure to a very fast queue that gets executed at a shorter interval.

The SQL expressions and context substitution variable are not useful in this instance as they can’t see the values on the columns. 

Perhaps, just perhaps, you can get away with not using a bg job by using an autonomous transaction? Updating a row while inserting to it is always tricky, but there are times that using autonomous transaction has got the job done. 

https://www.oreilly.com/library/view/oracle-plsql-programming/9780596805401/ch14s03.html