Script to delete the employee record using API in oracle

--Create Table

create table papf_16082018 (person_id number, employee_number varchar2(10));

--Insert Data

insert into papf_16082018 values (53687,'215677');

commit;

update apps.fnd_user set employee_id=null where employee_id in

                (select distinct p.person_id from apps.per_all_people_f p,papf_16082018 i where p.employee_number=i.employee_number);

commit;

               

set serveroutput on;

DECLARE

  -- Input Variables

  l_validate BOOLEAN    := FALSE;

  l_effective_date DATE := sysdate;

  l_person_id                 NUMBER    := 0;

  l_perform_predel_validation BOOLEAN   := FALSE;

  -- Output Variables

  l_person_org_manager_warning VARCHAR2(2000);

BEGIN

--Capture in Cursor

declare   

Cursor res is

  SELECT person_id,employee_number from papf_16082018;

--Start Loop

 BEGIN

  FOR rc in res

    LOOP

                BEGIN

        --API Update

                                --  Calling API HR_PERSON_API.DELETE_PERSON

                                                hr_person_api.delete_person(p_validate         => l_validate ,

                              p_effective_date             => l_effective_date ,

                              p_person_id                  => rc.person_id ,

                              p_perform_predel_validation  => l_perform_predel_validation ,

                              p_person_org_manager_warning => l_person_org_manager_warning );

                                                ddbms_output.put_line('Employee deleted successfully. Person ID ' || rc.person_id || ' Employee Number ' || rc.employee_number);

                                                COMMIT;

                EXCEPTION

    WHEN OTHERS THEN

    DBMS_OUTPUT.put_line ('Unable to delete Employee : '||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));

    END;       

    END LOOP;

               

 DBMS_OUTPUT.put_line ('Process Completed');

END;

EXCEPTION

WHEN OTHERS THEN

  dbms_output.put_line('Error : ' || sqlerrm);

END;

/

Employee data extract based on business group

SELECT

     hou.name                business_unit,

     papf1.first_name,

     papf1.middle_names,

     papf1.last_name,

     papf1.full_name         emp_full_name,

     pj.name                 employeee_job_desc,

     pj.attribute16          wd_job,

     papf1.employee_number   employee_number,

     papf1.email_address     employee_email_hr,

     fu.email_address        employee_email_fnd,

     fu.user_name            user_name,

     papf2.first_name        manager_first_name,

     papf2.last_name         manager_last_name,

     papf2.middle_names      manager_middle_name,

     papf2.full_name         manager_full_name,

     papf2.employee_number   manager_empno,

     papf2.email_address     manager_email

 FROM

     apps.per_people_x papf1,

     apps.per_people_x papf2,

     apps.hr_organization_units_v hou,

     apps.per_all_assignments_f paaf1,

     apps.per_jobs pj,

     apps.fnd_user fu

 WHERE

     1 = 1

     AND hou.organization_id = papf1.business_group_id

     AND papf1.person_id = paaf1.person_id

     AND paaf1.job_id = pj.job_id (+)

     AND paaf1.supervisor_id = papf2.person_id (+)

     AND papf1.person_id = fu.employee_id (+)

     AND hou.type = 'BG'

     AND papf1.business_group_id = p_business_group_id

     AND trunc(SYSDATE) BETWEEN trunc(paaf1.effective_start_date) AND trunc(paaf1.effective_end_date)

Script to get dependents for Employee

CREATE OR REPLACE FUNCTION APPS.HR_GET_DEPENDENT(p_element_entry_id IN NUMBER,
    p_contact_number IN NUMBER,
p_data_field IN VARCHAR2)

RETURN VARCHAR2 IS
l_contact_name VARCHAR2(300);


CURSOR C1 IS
SELECT DEP.Full_Name Contact_Full_Name,
       DEP.National_Identifier,
       PCP.Name    PCP,
       LOOK.Meaning,
       DPNT.Cvg_Strt_Dt Effective_Start_Date,
       DPNT.Cvg_Thru_Dt Effective_End_Date
  FROM PER_PEOPLE_X              PER,
       PER_ASSIGNMENTS_X         ASG,
       PAY_ELEMENT_ENTRIES_X     ENTRY,
       BEN_PRTT_ENRT_RSLT_X      PEN,
       BEN_ELIG_CVRD_DPNT_X      DPNT,
       BEN_PRMRY_CARE_PRVDR_X    PCP,
       PER_PEOPLE_X              DEP,
       PER_CONTACT_RELATIONSHIPS REL,
       HR_LOOKUPS                LOOK
 WHERE ENTRY.Element_Entry_Id = P_Element_Entry_Id
   AND ENTRY.Assignment_Id = ASG.Assignment_Id
   AND ASG.Person_Id = PER.Person_Id
   AND PER.Person_Id = PEN.Person_Id
   AND PEN.Prtt_Enrt_Rslt_Id  = DPNT.Prtt_Enrt_Rslt_Id
   AND DPNT.Elig_Cvrd_Dpnt_Id = PCP.Elig_Cvrd_Dpnt_Id(+)
   AND DPNT.Dpnt_Person_Id = DEP.Person_Id
   AND REL.Person_Id = PER.Person_Id
   AND REL.Contact_Person_Id = DEP.Person_Id
   AND LOOK.Lookup_Type = 'CONTACT'
   AND REL.Contact_Type = LOOK.Lookup_Code;

x number := 0;

BEGIN

FOR dep_rec IN C1 LOOP
x := x + 1;

IF x = p_contact_number THEN
IF p_data_field = 'NAME' THEN
   l_contact_name := dep_rec.contact_full_name;
ELSIF p_data_field = 'SSN' THEN
   l_contact_name := dep_rec.national_identifier;
ELSIF p_data_field = 'PCP' THEN
   l_contact_name := dep_rec.pcp;
ELSIF p_data_field = 'TYPE' THEN --relationship_type
   l_contact_name := dep_rec.meaning;
ELSIF p_data_field = 'START' THEN -- start
   l_contact_name := dep_rec.effective_start_date;
ELSIF p_data_field = 'END' THEN -- end
   l_contact_name := dep_rec.effective_end_date;
END IF;

EXIT;

END IF;


END LOOP;

       RETURN l_contact_name;

EXCEPTION WHEN OTHERS THEN
     RETURN null;
END HR_GET_DEPENDENT;
/