Introduction:

In oracle HRMS there are two kind of unique identifier for employee

• Employee Number
• Employee Assignment Number

Here we’ll talk about Employee Assignment Number :

The employee assignment number uniquely identifies every assignment that exists within your Business Group. An employee can have one or more than one assignment, and therefore more than one assignment number .

Oracle

Goal:

changes the status of an employee assignment to a “Suspended” using HR_ASSIGNMENT_API.SUSPEND_EMP_ASG API.

Forms Or Pages:

Prerequisites:

• The assignment must be an employee assignment, and must exist on the effective date

Example:

API to suspend assignment in oracle HRMS

API IN/OUT Parameters :

— IN
— p_validate IN NUMBER Optional Default FALSE
— p_effective_date IN DATE Required
— p_datetrack_update_mode IN VARCHAR2 Required
— p_assignment_id IN NUMBER Required
— p_change_reason IN VARCHAR2 Optional Default hr_api.g_varchar2
— p_assignment_status_type_id IN NUMBER Optional
default hr_api.g_number
— IN OUT
— p_object_version_number IN OUT NOCOPY NUMBER
— OUT x_return_status OUT VARCHAR2(1)
— p_effective_start_date OUT NOCOPY DATE
— p_effective_end_date OUT NOCOPY DAT

API Parameters clarifications:

• @param p_validate If true, then validation alone will be performed and the database will remain unchanged. If false and all validation checks pass, then the database will be modified.
• @param p_effective_date Determines when the DateTrack operation comes into force.
• @param p_datetrack_update_mode Indicates which DateTrack mode to use when updating the record. You must set to either UPDATE, CORRECTION, UPDATE_OVERRIDE or UPDATE_CHANGE_INSERT. Modes available for use with a particular record depend on the dates of previous record changes and the effective date of this change.
• @param p_assignment_id Identifies the assignment record to be modified.
• @param p_change_reason Reason for the assignment status change. If there is no change reason the parameter can be null. Valid values are defined in the EMP_ASSIGN_REASON lookup type.
• @param p_object_version_number Pass in the current version number of the assignment to be updated. When the API completes if p_validate is false, will be set to the new version number of the updated assignment. If p_validate is true will be set to the same value which was passed in.
• @param p_assignment_status_type_id The new assignment status. The new status must have a system status of SUSP_ASSIGN. If the assignment status is already a type of SUSP_ASSIGN this API can be used to set a different suspend status. If this parameter is not explicitly passed, the API uses the default SUSP_ASSIGN status for the assignment’s business group.
• @param p_effective_start_date If p_validate is false, then set to the effective start date on the updated assignment row which now exists as of the effective date. If p_validate is true, then set to null.
• @param p_effective_end_date If p_validate is false, then set to the effective end date on the updated assignment row which now exists as of the effective date. If p_validate is true, then set to null.

API Calling example:

DECLARE
  CURSOR emp_assign_cur
  IS
    SELECT paaf.assignment_number, paaf.assignment_id, paaf.object_version_number
    FROM   per_all_people_f per, per_all_assignments_f paaf
    WHERE  per.person_id = paaf.person_id
    AND    paaf.primary_flag = 'Y'
    AND    paaf.assignment_type = 'E'
    AND    paaf.assignment_status_type_id IN (1, 2)
    AND    sysdate BETWEEN per.effective_start_date AND per.effective_end_date
    AND    sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND    per.employee_number = '923';
  l_err_msg                      VARCHAR2 (1000) := NULL;
  l_effective_start_date         DATE;
  l_effective_end_date           DATE;
  
BEGIN
  --Initialize Session
  fnd_global.apps_initialize (user_id => 1318, -- OPERATIONS
                              resp_id => 50532, -- Human Resources, Vision Enterprises
                              resp_appl_id => 800 -- Human Resources
                                                                                  );
  FOR i IN emp_assign_cur
  LOOP
    BEGIN
      l_effective_start_date := NULL;
      l_effective_end_date := NULL;
      hr_assignment_api.suspend_emp_asg (p_validate                    => FALSE
                                        ,p_effective_date              => to_date ('07-APR-2019')
                                        ,p_datetrack_update_mode       => 'UPDATE'
                                        ,p_assignment_id               => i.assignment_id
                                        ,p_change_reason               => NULL
                                        ,p_object_version_number       => i.object_version_number
                                        ,p_assignment_status_type_id   => 2
                                        ,p_effective_start_date        => l_effective_start_date
                                        ,p_effective_end_date          => l_effective_end_date);
      --
      COMMIT;
      --
      dbms_output.put_line ('***************************');
      dbms_output.put_line ('Assignement No: '|| i.assignment_number || ' has been Suspended !!');
      dbms_output.put_line ('***************************');
      --
    EXCEPTION
      WHEN OTHERS THEN
        l_err_msg := substr (sqlerrm, 0, 1000);
        dbms_output.put_line ('***************************');
        dbms_output.put_line ('There is exception has been raised from API for assignment No : ' || i.assignment_number || ' with error message: ' || l_err_msg);
        dbms_output.put_line ('***************************');
    END;
  END LOOP;
END;

Final Result:

[convertkit form=1415728]