Oracle Apps R12 HRMS Update API Position Status = 'Invalid'

---single position
declare
l_effective_date           date := sysdate ;
l_position_id              per_positions.position_id%type := 551441;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;
--
begin
   ---   
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
   --l_effective_date         := trunc(sysdate);
   --l_pos_number             := '80304589.';
   --l_pos_title_code         := 'MECH_TECH';
   --l_object_version_number  := 3;

SELECT OBJECT_VERSION_NUMBER INTO l_object_version_number
FROM HR_POSITIONS_F
WHERE POSITION_ID = L_POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;

   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status       
                  ,p_name                          => l_pos_name
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                     
                  ,p_object_version_number              => l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                      
     dbms_output.put_line('Update Successful : ');
     --Important: regenerate_position_name needs to be called when there is a name update
     --                     
     apps.hr_position_api.regenerate_position_name(l_position_id);
     --                 
     dbms_output.put_line('Regenerate Successful : ');
                    
   if l_object_version_number is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed');
      rollback;   
   else
      commit;
   end if; 
--
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
end;          


--with cursor

declare
l_effective_date           date := sysdate ;
--l_position_id              per_positions.position_id%type := 3649;
l_status                   varchar2(40) := 'INVALID';
l_eff_start_date           date;
l_eff_end_date             date;
l_validate_mode            boolean := false;
l_object_version_number    hr_all_positions_f.object_version_number%type;
l_position_definition_id   number;
l_pos_name                 per_positions.name%type;
l_pos_title_code           fnd_lookup_values.lookup_code%type;
l_datetrack_mode VARCHAR2(20);
l_valid_grades_changed_warning BOOLEAN;

CURSOR C1 IS

select POSITION_ID , OBJECT_VERSION_NUMBER , NAME, STATUS
from HR_POSITIONS_F
where ORGANIZATION_ID in (101112,144121)
;
--
begin
   ---
 
   FOR I IN C1 LOOP 
   l_datetrack_mode         := 'UPDATE';
   l_position_definition_id := NULL;
   l_eff_start_date         := NULL;
   l_eff_end_date           := NULL;
   l_pos_name               := NULL;
 


/*SELECT OBJECT_VERSION_NUMBER, NAME INTO l_object_version_number, l_pos_name
FROM HR_POSITIONS_F
WHERE POSITION_ID = I.POSITION_ID
AND l_effective_date  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
*/
   --
   hr_position_api.update_position
                 ( p_validate                       => l_validate_mode
                  ,p_position_id                  => I.POSITION_ID--l_position_id
                  ,p_effective_start_date     => l_eff_start_date
                  ,p_effective_end_date      => l_eff_end_date
                  ,p_position_definition_id   => l_position_definition_id
                  ,p_valid_grades_changed_warning => l_valid_grades_changed_warning
                  ,p_status                          => l_status     
                  ,p_name                          => I.NAME
                  --,p_segment1                    => l_segment1
                  --,p_segment2                    => l_pos_title_code                   
                  ,p_object_version_number              => I.OBJECT_VERSION_NUMBER--l_object_version_number
                  ,p_effective_date             => l_effective_date
                  ,p_datetrack_mode         => l_datetrack_mode
                 );
                     
     dbms_output.put_line('Update Successful : '||I.POSITION_ID);
     --Important: regenerate_position_name needs to be called when there is a name update
     --                   
     apps.hr_position_api.regenerate_position_name( I.POSITION_ID);--l_position_id);
     --               
     dbms_output.put_line('Regenerate Successful : '||I.POSITION_ID);
               
   if I.OBJECT_VERSION_NUMBER is null then
      dbms_output.put_line('hr_position_api.update_position API Error: '||sqlerrm||' '||I.POSITION_ID);
      rollback;
   elsif l_valid_grades_changed_warning then
      dbms_output.put_line('Warning: Grade changed'||I.POSITION_ID);
      rollback; 
   else
      commit;
   end if;
--
 END LOOP; 
exception
  when others then
     dbms_output.put_line('hr_position_api.update_position API failed with error :'||sqlerrm);
     rollback;
     
end;