2 Replies Latest reply on Oct 17, 2017 3:25 PM by 3173025

    Issue in updating the grades description

    3173025

      Hi All,

       

      i have created the grades. Now i want to update the DFF in the grades (SALARY GRADE) marked in the below screenshot.

      i have developed a program to update. When i execute this procedure in backend, only one grade getting updated each time and for other record getting error as

      error is ORA-20001: HR_6153_ALL_PROCEDURE_FAIL: N, PROCEDURE,   per_grd_shd.constraint_error, N, STEP, 20

       

      If i run the procedure again then one more record getting updated and then the error is same as above.

      Procedure is placed below. Please help how all the records will get updated at a time.

       

      create OR REPLACE procedure xx_salary_grades_enter

      is

      l_object_version_number number;

      l_grade_definition_id number :=null;

      l_name VARCHAR2(100);

      l_concat_segments varchar2(100);

       

       

      cursor c1 is

      select pg.grade_id,pg.name,xcgs.salary_grade,PG.SEQUENCE,pg.date_from,pg.object_version_number

      from per_grades pg,

      XX_CREATE_GRADES_STG xcgs

      where pg.name=XCGS.SALARY_STRUCTURE||'.'||XCGS.JOB_CODE

      and pg.INFORMATION8 IS   NULL;

      --AND PG.NAME=nvl(p_grade,pg.name);

       

       

      CREC C1%rowtype;

       

       

      begin

      FOR CREC IN C1

      --open C1;

      loop

      --fetch C1 into CREC;

       

       

      BEGIN

       

      dbms_output.put_line('UPDATE NUMBER FOR GRADE'||' '||CREC.NAME||' '||'WITH'||' '||CREC.salary_grade);

       

       

      hr_grade_api.update_grade ( p_validate                => FALSE,

                                   p_grade_id => crec.grade_id,

      p_sequence => CREC.SEQUENCE,

      p_date_from => '01-JUL-2017',

                     P_INFORMATION_CATEGORY => 'GB_PQP_PENSERV_GRADE_INFO',

                      p_information2 => 'TPA',

        p_information8 => crec.salary_grade,

                       P_CONCAT_SEGMENTS     => l_concat_segments

         ,P_FORM_CALLING                  =>    FALSE,

               p_name        => l_name

               ,p_object_version_number => crec.object_version_number

               ,p_grade_definition_id   => l_grade_definition_id

                                          );

                                         commit;

                                         dbms_output.put_line('UPDATED SALARY GRADE AND commited');

                                         exception when others then

                                         rollback;

                                         dbms_output.put_line('error is'||' '||SQLERRM);                                

                                                          end;                            

                                                          end loop;

                                                          --close C1;

                                                          END;