Showing posts with label Human Resources (HR). Show all posts
Showing posts with label Human Resources (HR). Show all posts

Saturday, November 12, 2022

API to terminate Employee in Oracle Apps R12 (HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP, HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP, HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP )

 









In this post we have given a script to end date an employee. Below three API's are used. 

API:

HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP
HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP
HR_EX_EMPLOYEE_API.FINAL_PROCESS_EMP 

TEST Instance: R12.1.3

Script:

SET SERVEROUTPUT ON;
DECLARE
  l_obj                        NUMBER;
  l_period_of_service_id       NUMBER;
  l_dod_warning                BOOLEAN;
  l_supervisor_warning         BOOLEAN;
  l_event_warning              BOOLEAN;
  l_interview_warning          BOOLEAN;
  l_review_warning             BOOLEAN;
  l_recruiter_warning          BOOLEAN;
  l_pay_proposal_warning       BOOLEAN;
  l_entries_changed_warning    VARCHAR2(30);
  l_asg_future_changes_warning BOOLEAN;
  l_org_now_no_manager_warning BOOLEAN;
  l_termination_date           DATE := SYSDATE-5;
  l_last_std_process_date      DATE := SYSDATE-5;
  l_final_process_date         DATE := SYSDATE-5;
  ln_person_id                 NUMBER DEFAULT 39010; -- To be end dated
BEGIN
  SELECT period_of_service_id
        ,object_version_number
    INTO l_period_of_service_id
        ,l_obj
    FROM per_periods_of_service
   WHERE person_id = ln_person_id
     AND actual_termination_date IS NULL;
  /*
  -- HR_EX_EMPLOYEE_API.ACTUAL_TERMINATION_EMP populates the Leaving_Reason,
  -- Actual_Termination_Date and Last_Standard_Process_Date
  */
  hr_ex_employee_api.actual_termination_emp
          (p_validate                   => FALSE
          ,p_effective_date             => SYSDATE
          ,p_period_of_service_id       => l_period_of_service_id
          ,p_object_version_number      => l_obj
          ,p_actual_termination_date    => l_termination_date
          ,p_last_standard_process_date => l_last_std_process_date
          -- OUT Variables
          ,p_supervisor_warning         => l_supervisor_warning
          ,p_event_warning              => l_event_warning
          ,p_interview_warning          => l_interview_warning
          ,p_review_warning             => l_review_warning
          ,p_recruiter_warning          => l_recruiter_warning
          ,p_asg_future_changes_warning => l_asg_future_changes_warning
          ,p_entries_changed_warning    => l_entries_changed_warning
          ,p_pay_proposal_warning       => l_pay_proposal_warning
          ,p_dod_warning                => l_dod_warning
          );
  /*
  -- HR_EX_EMPLOYEE_API.UPDATE_TERM_DETAILS_EMP populates Accepted_Termination_Date,
  -- Notified_Termination_Date and Projected_Termination_Date
  */
  hr_ex_employee_api.update_term_details_emp
       (p_validate                   => FALSE
       ,p_effective_date             => SYSDATE
       ,p_period_of_service_id       => l_period_of_service_id
       ,p_object_version_number      => l_obj
       ,p_accepted_termination_date  => SYSDATE-5
       ,p_notified_termination_date  => SYSDATE-5
       ,p_projected_termination_date => SYSDATE-5
       );

  COMMIT;
  
  DBMS_OUTPUT.PUT_LINE('Employee is terminated…');
  
  /*
  -- Update Final Process Date for Terminated Employee --
  -- To Be executed only after the employee is terminated
  -- and has an become EX-EMP  
  */
  SELECT period_of_service_id,
         object_version_number,
         last_standard_process_date
    INTO l_period_of_service_id,
         l_obj,
         l_final_process_date
    FROM per_periods_of_service ppos
   WHERE period_of_service_id = ( SELECT MAX(period_of_service_id)
                                    FROM per_periods_of_service
                                   WHERE person_id = ppos.person_id
                                     AND actual_termination_date IS NOT NULL)
     AND person_id = ln_person_id
     AND final_process_date IS NULL;

  hr_ex_employee_api.final_process_emp
       (p_validate                   => FALSE
       ,p_period_of_service_id       => l_period_of_service_id
       ,p_object_version_number      => l_obj
       ,p_final_process_date         => l_final_process_date
       -- OUT Variables
       ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
       ,p_asg_future_changes_warning => l_asg_future_changes_warning
       ,p_entries_changed_warning    => l_entries_changed_warning
       );
  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Final Process Date is updated for Terminated Employee…');  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error while terminating Employee : '||SQLERRM);
END;
/

Testing:





Saturday, November 12, 2022 by Team search · 0

Monday, October 26, 2020

API to load Employee Address in R12 (HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS)

 

 

 

 

 

 

In this post, we have given a sample script to associate an address to existing employee in R12.

Tested Instance: R12.2

API: HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS

TABLES:

  1. per_all_people_f
  2. per_addresses

 Query to Get list of defined Address Styles: 

SELECT *
      FROM fnd_descr_flex_contexts
     WHERE descriptive_flexfield_name  = 'Address Structure'
       AND descriptive_flex_context_code = :style
       AND enabled_flag                  = 'Y'
       AND application_id                = 800;

Script: 

SET SERVEROUTPUT ON;
DECLARE 
  lrec_person_details    per_all_people_f%ROWTYPE;
  ln_object_version_num  NUMBER;
  ln_address_id          NUMBER;
  ln_exists              NUMBER;
  lv_employee_num        VARCHAR2(200) DEFAULT '123456';
  
BEGIN 

  SELECT papf.*
    INTO lrec_person_details
    FROM per_all_people_f papf
   WHERE papf.employee_number = lv_employee_num
     AND current_employee_flag = 'Y'
     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 
     
  HR_PERSON_ADDRESS_API.CREATE_PERSON_ADDRESS
        (p_validate              => FALSE,
         p_effective_date        => lrec_person_details.effective_start_date,
         p_person_id             => lrec_person_details.person_id,
         p_primary_flag          => 'Y',
         p_style                 => 'GENERIC',
         p_date_from             => SYSDATE, 
         p_address_line1         => '11, TEST ADDRESS LINE',
         p_address_line2         => NULL,
         p_address_line3         => NULL,
         p_country               => 'MY',
         p_postal_code           => '40400',
         p_region_1              => 'SELANGOR',
         p_address_id            => ln_address_id,
         p_object_version_number => ln_object_version_num
        );
   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

   SELECT COUNT(1)
     INTO ln_exists
     FROM per_addresses
    WHERE address_id = ln_address_id
      AND person_id = lrec_person_details.person_id ;
   IF ln_exists > 0
   THEN 
     DBMS_OUTPUT.PUT_LINE('Address successfully attached to person');   
   ELSE 
     DBMS_OUTPUT.PUT_LINE('Address assignment failed'); 
   END IF;
END;

Monday, October 26, 2020 by Team search · 1

Monday, October 12, 2020

API to delete an Employee Phone Number details from PER_PHONES (HR_PHONE_API.DELETE_PHONE)

 

 

 

 

 

 

            In this post, we have a given an script to delete the phone record details from Oracle Application. This comes handy during operating unit deactivation and need to delete all sensitive details of the employees from database.

API: HR_PHONE_API.DELETE_PHONE

Tested Instance: R12.2

Table Impacted: PER_PHONES and PER_ALL_PEOPLE_F

Script: 

SET SERVEROUTPUT ON;
DECLARE   
  CURSOR cur_phone_details (cv_emp_number  VARCHAR2)
  IS 
  SELECT pp.phone_id,
         pp.object_version_number,
         pp.phone_number
    FROM per_phones pp,
         per_all_people_f papf 
   WHERE papf.person_id = pp.parent_id 
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND pp.parent_table = 'PER_ALL_PEOPLE_F'
     AND papf.employee_number = cv_emp_number;
  
  lv_employee_number       VARCHAR2(200)  DEFAULT '123456';
  ln_count                 NUMBER;
BEGIN

  FOR phone_rec in cur_phone_details (lv_employee_number)
  LOOP
    ln_count := 0;
    
    HR_PHONE_API.DELETE_PHONE
        (
         p_validate => FALSE,
         p_phone_id => phone_rec.phone_id,
         p_object_version_number => phone_rec.object_version_number
        );
        
    SELECT COUNT(1)
      INTO ln_count
      FROM per_phones pp
     WHERE phone_id = phone_rec.phone_id;

    IF ln_count > 0 
    THEN    
      DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Failed!!');
    ELSE 
      DBMS_OUTPUT.PUT_LINE('Phone: '||phone_rec.phone_number||' Status: Success!!');
    END IF;         
  END LOOP;     
  
END;

Monday, October 12, 2020 by Team search · 0

Subscribe to: Posts (Atom)

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.


All Rights Reserved Oracle Apps Knowledge Sharing | Home by Team Search