9 Replies Latest reply: Mar 4, 2013 10:54 AM by progenitor1987 RSS

    Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.

    progenitor1987
      Hi,
      Currently the following fields are getting populated in assignment form depending on the Position name.
      1) Payroll
      2) Salary Basis
      3) Working Hours
      4) Frequency

      Now our client want to disable the position field. They want to store these information in Job DFF field. So going forward the above mentioned field need to be populated depending on the Job name in Core HR forms and Self Service pages. I have tried dynamic trigger and API hook. But none of them are working.

      Regards,
      Rakesh.
        • 1. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
          Sanjay Singh
          Hi Rakesh,

          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.

          Thanks,
          Sanjay
          • 2. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
            progenitor1987
            Hi Sanjay,

            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.


            Regards,
            rakesh
            • 3. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
              Sanjay Singh
              Hi Rakesh,

              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
              ON per_all_assignments_f
              FOR EACH ROW
              WHEN :OLD.job_id != :NEW.job_id
              DECLARE
              l_payroll_id     NUMBER;
              l_salary_basis_id     NUMBER;
              -- ect for other fields which are stored in Job DFF
              BEGIN

              -- fetch the values for the new Job_id from the Job DFF
              select attribute1, attribute2
              into l_payroll_id, l_salary_basis_id
              from per_jobs
              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;
              EXCEPTION
              WHEN OTHERS THEN
              :NEW.PAYROLL_ID := NULL;
              :NEW.salary_basis_id := NULL;

              end;


              Thanks,
              Sanjay

              Edited by: Sanjay Singh on Feb 28, 2013 10:47 AM
              • 4. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                progenitor1987
                Hi Sanjay,
                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.

                Condition
                ----------------------------------------------------------------
                Trigger Event : WHEN-NEW-ITEM-INSTANCE
                Trigger Object : ASSGT.JOB_NAME
                Condition : :SYSTEM.RECORD_STATUS IN ('CHANGED')


                Actions
                ----------------------------------------------------------------
                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.

                Regards,
                Rakesh.
                • 5. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                  Sanjay Singh
                  Hi Rakesh,

                  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.

                  Cheers,
                  Sanjay
                  • 6. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                    progenitor1987
                    Hi,

                    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?


                    Regards,
                    Rakesh.
                    • 7. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                      Sanjay Singh
                      Hi Rakesh,

                      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.

                      Thanks,
                      Sanjay
                      • 8. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                        progenitor1987
                        Hi Sanjay,
                        Thank you for your help.

                        Regards,
                        Rakesh.

                        Edited by: progenitor1987 on 04-Mar-2013 08:53
                        • 9. Re: Autopopulating PER_ALL_ASSIGNMENTS_F column fields depending on Job Id.
                          progenitor1987
                          It is not feasible by form personalization