This content has been marked as final. Show 9 replies
There are various ways of achieving this requirement.
Option 1 - You can use form personalization
Whenever the Job field is changed the event which gets fired by default is WHEN NEW ITEM INSTANCE for ASSGT.POSITION_NAME. You can use this event to default the values based on values stored in Job DFF. You will have to write simple functions which based on Job_id/Job_name bring the values for dersired field and in personalization set the item property value to the function value fetch.
Option 2 - You can write a trigger on per_all_assignments_f on update as well as insert FOR each row which based on the :NEW.JOB_ID you can set the :NEW.PAYROLL_ID, :NEW.SALARY_BASIS_ID etc and commit you transaction.
Hope it helps.
Thank you for you quick reply.
1) I have already tried Dynamic Trigger. We can only feed input parameter in Dynamic trigger. If we set :new.normal_hours as IN parameter then we cannot reset it in the function. In another way if we make it IN OUT parameter then the trigger will throw an error saying it is not a valid setup.
We can set up legislation_code and business_group_id as OUT/RETURN variable.
Are you suggesting to use dynamic trigger in any other way?
2) I am still trying the other one which is form personalization.
Can you try the below code and see if it solves your issue.
CREATE OR REPLACE TRIGGER xx_default_values
BEFORE INSERT OR UPDATE of job_id
FOR EACH ROW
WHEN :OLD.job_id != :NEW.job_id
-- ect for other fields which are stored in Job DFF
-- fetch the values for the new Job_id from the Job DFF
select attribute1, attribute2
into l_payroll_id, l_salary_basis_id
where 1 = 1
and job_id = :NEW.JOB_ID;
-- Set the values for payroll and salary basis
:NEW.PAYROLL_ID := l_payroll_id;
:NEW.salary_basis_id := l_salary_basis_id;
WHEN OTHERS THEN
:NEW.PAYROLL_ID := NULL;
:NEW.salary_basis_id := NULL;
Edited by: Sanjay Singh on Feb 28, 2013 10:47 AM
Thank you for the reply. But we are not looking for any trigger option. Because if we make such trigger on oracle seeded table then later on support from ORACLE would not be possible. Rather if we can do via form personalization then it would be better.
So I tried it in the following way(form personalization). But it is not working. Please correct me if I am wrong at any steps. Right now I am trying to fetch a constant value after changing the job.
Trigger Event : WHEN-NEW-ITEM-INSTANCE
Trigger Object : ASSGT.JOB_NAME
Condition : :SYSTEM.RECORD_STATUS IN ('CHANGED')
Seq - 1
Type - Property
Object Type - Item
Target object - ASSGT.NORMAL_HOURS
Property Name - VALUE
Value - 32.12
But after this when I am changing job name it is not changing the normal hours.
If you see my earlier post I had suggested the personalization option too, I had provide the trigger code based on your doubt in trigger so that it may be helpful.
The reason why your personalization is not working is because the WHEN_NEW_ITEM_INSTANCE fires before the data is entered.
Try this on WHEN_NEW_ITEM_INSTANCE on ASSGT.POSITION_NAME, the reason I am recommending this is once you update job it automatically fires this event (try the condition as 1=1), but the issue with this will be that this personalization will fire always when you will click on Position field. Solving your requirement with personalization will be a difficult one.
As far as Oracle is considered even your personalization will not be supported by them. Its on your business requirement that how cirtical it is, if its high priority there is no harm in extending the application provided its tested properly. You will hardly find any implementation where you are not customizing the EBS.
Than you Sanjay for your suggestion. I like the trigger option. Thanks again for that option and the way you are helping me.
The way you said it is working in form personalization. But the problem is that my client is going to disable the position field. So they want to use job field to autopopulate the fields. Now clicking on position will not be working as the position field will not be there. So I did the personalization on Job field.
Is it possible to make it enabled for job field?
It can't be enabled on the Job field as the job information will not be entered yet. You can enable on any other field like Location, Grade, etc if the position field is going to be disabled. I still feel that it will be better if you go-ahead with the trigger option as it will be the best for your requirement.
Also, please mark the earlier proposed options helpful/correct so that other too can use the same in case having similar issues.