SELECT paaf.effective_start_date
      ,paaf.object_version_number
      ,paaf.assignment_id
      ,paaf.assignment_number
      ,paaf.cagr_grade_def_id
      ,paaf.soft_coding_keyflex_id
      ,paaf.people_group_id
      ,paaf.special_ceiling_step_id
  INTO v_effective_start_date
      ,x_object_version_number
      ,v_assignment_id
      ,v_assignment_number
      ,x_cagr_grade_def_id
      ,x_soft_coding_keyflex_id
      ,x_people_group_id
      ,x_special_ceiling_step_id
  FROM per_all_people_f ppf, per_all_assignments_f paaf
 WHERE ppf.person_id = paaf.person_id
   AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND ppf.current_employee_flag = 'Y'
   AND ppf.person_type_id = (SELECT person_type_id
                               FROM per_person_types
                              WHERE user_person_type = 'Employee')
   AND paaf.object_version_number = (SELECT MAX (object_version_number)
                                       FROM per_all_assignments_f
                                      WHERE assignment_number = '12345')
   AND ppf.employee_number = '12345';
   
   
DECLARE
   x_object_version_number          NUMBER;
   x_people_group_id                NUMBER;
   x_soft_coding_keyflex_id         NUMBER;
   x_concatenated_segments          VARCHAR2 (500);
   x_special_ceiling_step_id        NUMBER;
   x_group_name                     VARCHAR2 (500);
   x_effective_start_date           DATE;
   x_effective_end_date             DATE;
   x_other_manager_warning          BOOLEAN;
   x_org_now_no_manager_warning     BOOLEAN;
   x_spp_delete_warning             BOOLEAN;
   x_entries_changed_warning        VARCHAR2 (500);
   x_tax_district_changed_warning   BOOLEAN;
BEGIN
   hr_assignment_api.update_emp_asg_criteria
            (p_validate                    => FALSE
            ,p_effective_date              => TRUNC (SYSDATE)     -- Mandatory
            ,p_datetrack_update_mode       => 'CORRECTION'        -- Mandatory
            ,p_assignment_id               => '21363'             -- Mandatory
            ,p_job_id                      => '45'
            ,p_position_id                 => ''                   -- OPTIONAL
            ,p_location_id                 => ''                   -- OPTIONAL
            ,p_organization_id             => ''                   -- OPTIONAL
            ,p_people_group_id             => x_people_group_id
            ,p_soft_coding_keyflex_id      => x_soft_coding_keyflex_id
            ,p_concatenated_segments       => x_concatenated_segments
            ,p_object_version_number       => x_object_version_number
                                                                  -- Mandatory
            ,p_special_ceiling_step_id     => x_special_ceiling_step_id
            ,p_group_name                  => x_group_name
            ,p_effective_start_date        => x_effective_start_date
            ,p_effective_end_date          => x_effective_end_date
            ,p_org_now_no_manager_warning  => x_org_now_no_manager_warning
            ,p_other_manager_warning       => x_other_manager_warning
            ,p_spp_delete_warning          => x_spp_delete_warning
            ,p_entries_changed_warning     => x_entries_changed_warning
            ,p_tax_district_changed_warning => x_tax_district_changed_warning
            );
   DBMS_OUTPUT.put_line ('SUCCESS');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;