PlSql - Terminate an employee in oracle R12 using standard API

Terminate an employee in oracle R12 using standard API


Here the leaving reason and the person type id should be careful on selecting. The leaving reason we can't able to give as we like.

The below api was used

1) hr_ex_employee_api.actual_termination_emp

 

2) hr_ex_employee_api.final_process_emp        

 

3) hr_ex_employee_api.update_term_details_emp


These 3 also should be executed else when we reverse terminate an employee it won't work. This will look at the payroll as well. Check with business or use the mentioned function terminate_employee below so that the payroll won't get affected.

Based on the link I validated - https://community.oracle.com/message/14155828#14155828

Also in the code below the business group id and the person type id is the mandatory one . the person type id 1320 is the current active employee in Oracle which is employee and in the api there passes the person type id as 1323 i.e the terminated record will be stored as Ex-employee

Below code works fine for me

/* - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -                               
Description:
Object Name: 3241_Datafix.adh
  - - - - - - - -
  W o r k   L o g                                                                
  - - - - - - - -            
 Work      Work Date                                                                     
 Case      dd-Mon-yyyy   Resource          Version  Description of Work...                                   
------      -----------  ----------        -------  ------------------------------------------
3241   07-JUL-2016  Sathish       1      Object genesis.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - */

SET SERVEROUTPUT ON;
Declare
   Cursor c_emp_cur Is
   SELECT pos.period_of_service_id,
                  pos.object_version_number,
                  pos.date_start ,
                  paf.person_id ,
                 paf.person_type_id
                 FROM apps.per_all_people_f paf,
                  apps.per_periods_of_service pos
                WHERE paf.person_id      =pos.person_id
                AND paf.business_group_id=89
                AND paf.person_id       IN       (768,
                  )
              AND paf.current_employee_flag = 'Y'
              AND paf.current_emp_or_apl_flag = 'Y'
              AND trunc(SYSDATE) BETWEEN paf.effective_start_date AND
             nvl(paf.effective_end_date, SYSDATE + 1)
             AND paf.person_type_id IN
             (SELECT person_type_id
                FROM apps.per_person_types
               WHERE upper(user_person_type) = 'EMPLOYEE');
               
               
               l_validate Boolean := False;
               l_period_of_service_id Number;
               l_object_version_number Number;
               l_actual_notice_period_date Date;
               l_effective_date date;                                          -- This should be a termination date
               l_supervisor_warning Boolean;
               l_event_warning Boolean;
               l_interview_warning Boolean;
               l_review_warning Boolean;
               l_recruiter_warning Boolean;
               l_asg_future_changes_warning Boolean;
               l_f_asg_future_changes_warning Boolean;
               l_pay_proposal_warning Boolean;
               l_dod_warning Boolean;
              l_final_process_date DATE ;   
               l_org_now_no_manager_warning Boolean;
               l_entries_changed_warning Varchar2(255);
               l_f_entries_changed_warning Varchar2(255);
               l_alu_change_warning Varchar2(255);
               l_person_type_id Number;
               l_last_std_process_date_out Date;   -- This should be a termination date

     cnt NUMBER := 0;
     l_errcount NUMBER := 0;
            begin
       
               For c_emp_rec in c_emp_cur
                                  Loop
                                     l_period_of_service_id := c_emp_rec.period_of_service_id;
                                     l_object_version_number := c_emp_rec.object_version_number;
                                     l_actual_notice_period_date := l_final_process_date ; --c_emp_rec.end_date;
                                     l_effective_date := l_final_process_date ; --sysdate;
                                    -- l_person_type_id := c_emp_rec.person_type_id;
                                    
                   Begin
                             hr_ex_employee_api.actual_termination_emp
                                   (p_validate => l_validate
                                   ,p_effective_date => l_effective_date
                                   ,p_period_of_service_id => l_period_of_service_id
                                   ,p_object_version_number => l_object_version_number
                                   ,p_actual_termination_date => l_actual_notice_period_date
                                   ,p_last_standard_process_date => l_actual_notice_period_date
                                   ,p_person_type_id => '1323'
                                   ,p_leaving_reason => 'R'
                                   ,p_last_std_process_date_out => l_last_std_process_date_out
                                   ,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
                                   ,p_alu_change_warning => l_alu_change_warning
                                   );
                                  
                            Below updating accepted termination date, notified termination date, projected termination date will affect the employee who resigned by getting final payout. The dates should not be equal to the same termination date so passing it as null      
                                  
From:

                             hr_ex_employee_api.update_term_details_emp
                                  (p_validate    => l_validate
                                  ,p_effective_date          => l_effective_date
                                  ,p_period_of_service_id        => l_period_of_service_id
                                  ,p_object_version_number      => l_object_version_number
                                  ,p_accepted_termination_date  => l_effective_date
                                  ,p_leaving_reason           => 'R'
                                  ,p_notified_termination_date   => l_effective_date
                                  ,p_projected_termination_date  => l_effective_date
                                  );
                                 
Modified To:

        hr_ex_employee_api.update_term_details_emp(p_validate                   => l_validate
                                                  ,p_effective_date             => l_effective_date
                                                  ,p_period_of_service_id       => l_period_of_service_id
                                                  ,p_object_version_number      => l_object_version_number
                                                  ,p_leaving_reason             => 'R' );    



                                 
  l_final_process_date := l_effective_date;
                            
                            
                         hr_ex_employee_api.final_process_emp
                                 (p_validate  => l_validate
                                 ,p_period_of_service_id  => l_period_of_service_id
                                 ,p_object_version_number => l_object_version_number
                                 ,p_final_process_date    => l_final_process_date
                                 ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                                 ,p_asg_future_changes_warning => l_f_asg_future_changes_warning
                                 ,p_entries_changed_warning => l_f_entries_changed_warning
                                 );
         cnt := cnt + 1;                       
        
     Exception
     WHEN OTHERS
         THEN
    DBMS_OUTPUT.put_line ('Error for employee person id : '|| c_emp_rec.person_id ||'Error message: ' || sqlerrm);
             ROLLBACK;
            l_errcount := l_errcount + 1;
     End;
  End Loop;
  commit;
   DBMS_OUTPUT.put_line ('Number of rows updated successfully : ' || cnt);
   DBMS_OUTPUT.put_line ('Total Error records which is rollback : ' || l_errcount);
   DBMS_OUTPUT.put_line (' Total number of records which need to be updated : ' || ( cnt + l_errcount ));
End;
/

same above as the function in which I used for termination

  FUNCTION terminate_employee(emp_rec IN OUT emprec
                             ,errbuff OUT VARCHAR2
                             ,retcode OUT VARCHAR2) RETURN BOOLEAN AS
    dt_end                         DATE := NULL;
    l_validate                     BOOLEAN := FALSE;
    l_period_of_service_id         NUMBER;
    l_object_version_number        per_all_people_f.object_version_number%TYPE;
    l_actual_notice_period_date    DATE;
    l_effective_date               DATE;
    l_supervisor_warning           BOOLEAN;
    l_event_warning                BOOLEAN;
    l_interview_warning            BOOLEAN;
    l_review_warning               BOOLEAN;
    l_recruiter_warning            BOOLEAN;
    l_asg_future_changes_warning   BOOLEAN;
    l_f_asg_future_changes_warning BOOLEAN;
    l_pay_proposal_warning         BOOLEAN;
    l_dod_warning                  BOOLEAN;
    --l_final_process_date           DATE := SYSDATE;           -- Commented by sathish for V2
    l_final_process_date           DATE;
    l_org_now_no_manager_warning   BOOLEAN;
    l_entries_changed_warning      VARCHAR2(255);
    l_f_entries_changed_warning    VARCHAR2(255);
    l_alu_change_warning           VARCHAR2(255);
    l_person_type_id               NUMBER;
    l_last_std_process_date_out    DATE;
    cnt                            NUMBER := 0;
    l_errcount                     NUMBER := 0;
 
    l_op_name            VARCHAR2(100);
    ln_business_group_id per_business_groups.business_group_id%TYPE;
 
    -- Local Constant Declaration
    c_h_comp_code CONSTANT VARCHAR2(10) := '21181';
    c_s_comp_code CONSTANT VARCHAR2(10) := '21301';
    c_h_op_name   CONSTANT VARCHAR2(100) := 'Operating unit 1';
    c_s_op_name   CONSTANT VARCHAR2(100) := 'Operting unit 2 ';
    c_person_type_id per_person_types.person_type_id%TYPE;
 
    CURSOR c_emp_cur IS
      SELECT pos.period_of_service_id
            ,pos.object_version_number
            ,pos.date_start
            ,paf.person_id --,paf.person_type_id
        FROM apps.per_all_people_f       paf
            ,apps.per_periods_of_service pos
       WHERE paf.person_id = pos.person_id
         AND paf.business_group_id = ln_business_group_id --89
         AND paf.employee_number = emp_rec.employee_number
         AND trunc(SYSDATE) BETWEEN paf.effective_start_date AND
             nvl(paf.effective_end_date, SYSDATE + 1)
         AND paf.person_type_id IN
             (SELECT person_type_id
                FROM apps.per_person_types
               WHERE upper(user_person_type) = g_employee_type) -- 'EMPLOYEE'
         AND emp_rec.status = g_val_terminate     -- Comment this line
         AND paf.current_employee_flag = 'Y'
         AND paf.current_emp_or_apl_flag = 'Y'
         AND paf.attribute10 IS NOT NULL;
 
  BEGIN
    fnd_file.put_line(fnd_file.log, 'Before Terminating Employee');
    fnd_file.put_line(fnd_file.log, 'emp_rec.employee_number      ' || emp_rec.employee_number);
    fnd_file.put_line(fnd_file.log, 'emp_rec.status               ' || emp_rec.status);
    fnd_file.put_line(fnd_file.log, 'emp_rec.hire_date           ' || emp_rec.hire_date);
    fnd_file.put_line(fnd_file.log, 'emp_rec.termination_date     ' || emp_rec.termination_date);
 
    -- Fetch the operating unit name based on the employee company code
    -- If company code is 21181, the operating unit to which employee belongs is OU
    IF (emp_rec.oracle_acct_segment1 = c_h_comp_code)
    THEN
      l_op_name := c_h_op_name;
      -- If company code is 21301, the operating unit to which employee belongs is  OU
    ELSIF (emp_rec.oracle_acct_segment1 = c_slk_comp_code)
    THEN
      l_op_name := c_s_op_name;
   
      -- For other company codes, make the OU to NULL
    ELSE
      l_op_name := NULL;
    END IF;
    fnd_file.put_line(fnd_file.log
                     ,'The employee belongs to operating unit           ' || l_op_name);
    -- Fetch the business group id and person type id for employee person type based on the operating unit
 
    SELECT haou.business_group_id
      INTO ln_business_group_id
      FROM hr.hr_all_organization_units haou
     WHERE haou.name = l_op_name;
 
    fnd_file.put_line(fnd_file.log
                     ,'The employee belongs to business group id          ' ||
                      ln_business_group_id);
 
    SELECT person_type_id
      INTO c_person_type_id
      FROM per_person_types
     WHERE business_group_id = ln_business_group_id
       AND user_person_type = g_ex_employee_type;  -- 'Ex-employee'
 
    FOR c_emp_rec IN c_emp_cur LOOP
      l_period_of_service_id  := c_emp_rec.period_of_service_id;
      l_object_version_number := c_emp_rec.object_version_number;
      -- l_actual_notice_period_date := c_emp_rec.end_date;
      l_effective_date := nvl(emp_rec.termination_date, SYSDATE);
      l_last_std_process_date_out := nvl(emp_rec.termination_date, SYSDATE);     -- Added by Sathish for V2
      -- l_person_type_id := c_emp_rec.person_type_id;
   
      BEGIN
        hr_ex_employee_api.actual_termination_emp(p_validate                   => l_validate
                                                 ,p_effective_date             => l_effective_date
                                                 ,p_period_of_service_id       => l_period_of_service_id
                                                 ,p_object_version_number      => l_object_version_number
                                                 ,p_actual_termination_date    => l_effective_date
                                                 ,p_last_standard_process_date => l_effective_date
                                                 ,p_person_type_id             => c_person_type_id
                                                 ,p_leaving_reason             => 'R'
                                                 ,p_last_std_process_date_out  => l_last_std_process_date_out
                                                 ,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
                                                 ,p_alu_change_warning         => l_alu_change_warning);
     
     /*   hr_ex_employee_api.update_term_details_emp(p_validate                   => l_validate
                                                  ,p_effective_date             => l_effective_date
                                                  ,p_period_of_service_id       => l_period_of_service_id
                                                  ,p_object_version_number      => l_object_version_number
                                                  ,p_accepted_termination_date  => l_effective_date
                                                  ,p_leaving_reason             => 'R'
                                                  ,p_notified_termination_date  => l_effective_date
                                                  ,p_projected_termination_date => l_effective_date);  */
                                                 
        hr_ex_employee_api.update_term_details_emp(p_validate                   => l_validate
                                                  ,p_effective_date             => l_effective_date
                                                  ,p_period_of_service_id       => l_period_of_service_id
                                                  ,p_object_version_number      => l_object_version_number
                                                  ,p_leaving_reason             => 'R' );                                                 
     
        l_final_process_date := l_last_std_process_date_out;
     
        hr_ex_employee_api.final_process_emp(p_validate                   => l_validate
                                            ,p_period_of_service_id       => l_period_of_service_id
                                            ,p_object_version_number      => l_object_version_number
                                            ,p_final_process_date         => l_final_process_date
                                            ,p_org_now_no_manager_warning => l_org_now_no_manager_warning
                                            ,p_asg_future_changes_warning => l_f_asg_future_changes_warning
                                            ,p_entries_changed_warning    => l_f_entries_changed_warning);
        cnt := cnt + 1;
     
      EXCEPTION
        WHEN OTHERS THEN
          fnd_file.put_line(fnd_file.log
                           ,'Error for employee person id : ' || c_emp_rec.person_id ||
                            'Error message: ' || SQLERRM);
          ROLLBACK;
          l_errcount      := l_errcount + 1;
          g_error_message := 'Error(' || SQLCODE || ') ' || SQLERRM;
          g_key_info      := NULL;
          g_error_code    := 'terminate_employee';
          RETURN FALSE;
      END;
    END LOOP;
    COMMIT;
    fnd_file.put_line(fnd_file.log, 'After Terminating Employee');
 fnd_file.put_line(fnd_file.log, 'Number of persons terminated successfully : ' || cnt);
    fnd_file.put_line(fnd_file.log, 'Total Error records which is Rollback : ' || l_errcount);
    fnd_file.put_line(fnd_file.log
                     ,' Total number of persons which need to be terminated : ' || (cnt + l_errcount));
    RETURN TRUE;
  END terminate_employee;

How to validate: Check the passed parameter date and reason in below tables


SELECT * FROM APPS.per_all_people_f paf where paf.person_id       IN       (768,
                     15148);

select * from per_periods_of_service where person_id       IN       (768,
                                         15148);
select * from apps.per_all_assignments_f where person_id       IN       (768,
                     15148);

Front end:

So when you terminate using this API (as you mentioned), it uses a few functions. The best way to check this is in the application as the user above mentioned.

 

The pathway is this (assuming your menus aren't custom): US Super HRMS Manager (or comparable responsibility) > Enter and Maintain (Form)> Others(button) >End Employment. This will show if you properly updated the termination dates including final process, last standard process and actual. It will also show you the leaving reason. These are from your #2. If you want to look it up in the table, the table is per_periods_of_service. It's just an update to insert the data. I don't think that there is a new row as well.

 

Note that once you update the final process date, the assignment record (per_all_assignments_f) will insert at least 1 row (a terminated record) and modify 1 row (to update the effective end date of the existing record). It will also insert a 'terminated' assignment record if your dates do not match.  The way to check this in the app is: US Super HRMS Manager (or comparable responsibility) > Enter and Maintain (Form)>Assignments (button). This might require date tracking depending on the level of checks you do.

 

Also as you mentioned, it will update per_people_f with an update and insert row.

 

Lastly, you can view the code from these API's using SQL to see the tables adjusted.

 

Good luck.

------------------------------------------------------------------------------------------------

ORACLE HRMS

-------------------------------

Terminations

----------------

FREQUENTLY ASKED QUESTIONS

-----------------------------------------------

31-JAN-2002

CONTENTS

---------------

1) Where do you terminate an employee in the application?

2) How do you terminate an employee?

3) What API’s are available for termination?

4) How do you rehire a terminated employee?

5) How do you perform a reverse termination?

6) How do you populate the Leaving Reason list of values on Termination form?

7) What is the significance of the fields in the Termination Dates zone on Termination form?

i.e. Notified, Projected, Actual, Last Standard Process, Final Process.

8) How do you terminate in a shared HR installation?

9) How do you change the end date (termination date) in a shared HR installation?

10)R11.5 – Why does terminating an employee produce error APP-BEN-92108?

11) How do you terminate in Self Service Human Resources?

12) How do you pay terminated employees in Oracle Payroll?

13) What happens to element entries upon termination?

14) Why do element entries remain end dated upon reverse termination?

QUESTIONS AND ANSWERS

---------------------------------------

1) Navigate to US HR or US HRMS Manager (or equivalent) > People > Enter and Maintain >

Others > End Employment.

2) On Terminate screen, select Actual Process Date and/or Final Process Date and click on

Terminate button. Optionally, enter ‘Leaving Reason,’ ‘Notified,’ ‘Projected,’ ‘Last Standard

Proces’ values.

3) Hr_ex_employee_api (File name: peexeapi.pkb)

This file contains three (3) procedures.....

hr_ex_employee_api.actual_termination_emp

hr_ex_employee_api.update_term_details_emp

hr_ex_employee_api.final_process_emp

Note: The API's have to be called in that order.

4) Ensure the Final Process Date field is populated on Terminate screen. Navigate back to

People form and change person type of Ex-employee to Employee from list of values.

5) On Terminate screen, datetrack to termination date and select Reverse Termination button.

6) This is controlled by Lookup Type ‘LEAV_REAS.’ Add any number of records to this

lookup. Navigation: US HRMS Manager > Other Definitions > Application Utilities

Lookups

7) Notification Date:

This is for information only and is optional.

Projected Date:

This is for information only and is optional.

Actual Date:

This corresponds to the actual date the employee left the organization.

When you DateTrack after this date the People form will show the person as an

Ex-Employee. This is required.

Last Standard Process Date:

Where Oracle Payroll is used this will be the date after which no further

payroll processing can occur. This is optional and can be left blank.

Element entries defined against the employee would be end dated with this

date rather than the actual date if they were defined with the standard rule

of Last Standard Process.

Final Process Date:

This will represent the last date for late payments for Oracle Payroll users.

An example would be where certain elements were paid in arrears - such

as commission. Element entries defined against the employee would be end dated

with this date rather than the actual date if they were defined with the

standard rule of Final Close.

8) In shared HR installations, you’ll navigate to the shared People form (PERWSEMP). Query

employee and populate End Date field.

Note: The navigation varies based on financial application you are using. For example:

Responsibility Navigation

--------------------------------- -------------------------------

PO Super User GUI Setup/Personnel/Employees

PA Projects Costing Super User GUI Setup/Human Resources/Employees

AP Manager GUI Employees/Enter Employees

9) This example uses Projects responsibility:

A. Select the Responsibility Project Billing Super User or Costing Super User.

B. Navigate: Setup -> Human Resources -> Employees

C. Query the employee.

D. Clear the End Date field.

E. Save.

F. Enter the new end-date.

G. Save.

10) You must define a default monthly payroll for each business group you maintain.

Benefit assignments are linked to the default monthly payroll in order to calculate premiums

for continuing benefits such as COBRA. (Oracle Development recommends defining a Monthly

payroll as default at the GRE level.)

The monthly default payroll does not need to be unique. If a monthly payroll is already

set up for your business group, you may choose to use that payroll as a default, rather than

setting up a new one.

To assign a default payroll for Benefits:

3.1. (N)Work Structures -> Organization -> Description.

3.2. Query the Organization.

3.3. Under Organization Classifications -> Business Group click on the

Others button.

3.4. Choose Benefits Defaults Enter the Monthly payroll you will use for

the default for Benefits.

11) Manager Self-Service > Termination (US) > Select employee > terminate

12) Ensure the terminated employee has an assignment status related to ‘Process’ payrolls. You

can confirm which statuses you have set under Work Structures > Status. Element entries on the

terminated employee should be non-recurring elements.

13) All element entries are end-dated.

14) US seeded elements presently set as 'Final Close,’ have a formula-based stop rule that fires

the first time the element is processed after actual termination. Since the elements are enddated

by the payroll run and not as a direct result of the termination, the reverse termination cannot

unendate the element entry with any certainty.

We have enabled 'Last Standard Process' for the US so you can create a new element and set the

termination rule to the new option.

You cannot change the termination rule for elements once entries and run results exist.

credit: cquick, ssnyder, merickso 
 

PlSql - Query to find the function name available in the active responsibility and active user which is not included in the menu exclusion list

PlSql - Query to find the function name available in the active responsibility and active user  which is not included in the menu exclusion list

SELECT frtl.responsibility_name,
       fr.responsibility_key,
       fm.menu_id,
       fm.menu_name,
       --menu.function_id,
       menu.prompt,
       fffv.user_function_name,
       fffv.function_name
       --,fffv.TYPE
  FROM (SELECT connect_by_root fmet.menu_id top_menu_id,
               fmet.menu_id                 menu_id,
               fmet.sub_menu_id,
               fmet.function_id,
               fmet.prompt
          FROM apps.fnd_menu_entries_vl fmet
        CONNECT BY PRIOR fmet.sub_menu_id = fmet.menu_id
                         AND PRIOR fmet.prompt IS NOT NULL) menu,
       apps.fnd_responsibility fr,
       apps.fnd_responsibility_tl frtl,
       apps.fnd_menus fm,
       apps.fnd_form_functions_vl fffv
 WHERE fr.menu_id = menu.top_menu_id
   AND fffv.function_id = menu.function_id
   AND fffv.TYPE <> 'SUBFUNCTION'
   AND menu.function_id IS NOT NULL
   AND menu.prompt IS NOT NULL
   AND fm.menu_id = menu.menu_id
   AND frtl.responsibility_id = fr.responsibility_id    
   AND menu.function_id NOT IN (SELECT ffvl.function_id
                                  FROM apps.fnd_resp_functions frf,
                                       applsys.fnd_responsibility_tl frt,
                                       apps.fnd_form_functions_vl ffvl
                                 WHERE
       frf.responsibility_id = frt.responsibility_id
                                   AND frf.action_id = ffvl.function_id
                                   AND frf.rule_type = 'F'
                                   AND
           frt.responsibility_name = frtl.responsibility_name)
   AND menu.menu_id NOT IN (SELECT fmv.menu_id
                              FROM apps.fnd_resp_functions frf,
                                   applsys.fnd_responsibility_tl frt,
                                   apps.fnd_menus_vl fmv
                             WHERE
       frf.responsibility_id = frt.responsibility_id
                               AND frf.action_id = fmv.menu_id
                               AND frf.rule_type = 'M'
                               AND
       frt.responsibility_name = frtl.responsibility_name)
       AND frtl.language='US'
       --AND frtl.responsibility_name='HCC PO SME'  AND fm.menu_id =78826
       AND fffv.function_name='XLA_MJE_SEARCH'  -- Function name
      and menu.function_id=25496    -- Function id
 AND fr.responsibility_id IN ( select  c.responsibility_id
     from apps.fnd_user_resp_groups_direct a,
       apps.fnd_user                    b,
       apps.fnd_responsibility_tl       c,
       apps.fnd_application_tl          d
 where a.user_id = b.user_id
   and a.responsibility_id = c.responsibility_id
   and c.application_id = d.application_id
   and (a.end_date > sysdate OR a.end_date is Null)
   and (b.end_date > sysdate OR b.end_date is Null)
   and c.language = d.language
   and c.language='US' )
and fr.responsibility_id not in (select ex.responsibility_id from fnd_resp_functions ex where ex.action_id!=25496)    -- Used for menu exclusions
 ORDER BY frtl.responsibility_name,fffv.user_function_name;

PlSql - Reverse terminate an employee using Oracle R12

The below code will be used to reverse terminate an employee, we received from Oracle.


Required to pass only person id as parameters

SET SERVEROUTPUT ON;


SET SERVEROUTPUT ON;

DECLARE

   CURSOR c_emp_cur
   IS
select person_id, actual_termination_date
from per_periods_of_service
where actual_termination_date is not null
AND person_id=18631;

l_validate Boolean := False;
l_actual_termination_date DATE;
l_person_id Number;  

 BEGIN

   FOR c_emp_rec IN c_emp_cur
   LOOP

      l_person_id := c_emp_rec.person_id;
      l_actual_termination_date := c_emp_rec.actual_termination_date;    

   DBMS_OUTPUT.PUT_LINE('l_person_id' ||l_person_id); 
   DBMS_OUTPUT.PUT_LINE('l_actual_termination_date' ||l_actual_termination_date); 
  
   
      BEGIN
      
                         hr_ex_employee_api.reverse_terminate_employee
                                 (p_validate  => l_validate
                                 ,p_person_id  => l_person_id
                                 ,p_actual_termination_date => l_actual_termination_date
                                 ,p_clear_details => 'Y'
                                 );
        DBMS_OUTPUT.PUT_LINE('DONE');

     Exception
        WHEN Others Then
        DBMS_OUTPUT.PUT_LINE( sqlerrm);
     End;
    
  End Loop;
   commit;
End;


O/p:

PL/SQL procedure successfully completed.
l_person_id18631
l_actual_termination_date24-DEC-16
DONE


Here tables affected is

select * from per_all_assignments_f where person_id=18631;
select * from per_all_people_f where employee_number='111999';
select * from per_periods_of_Service where person_id=18631;

The 2 records created in assignments,per_all_people_f, periods all got cleared.

If required much more details-

https://community.oracle.com/message/14155828#14155828