API/Script to create Oracle HRMS - Employee Extra Information details

create or replace PROCEDURE XX_EMP_RESIGNATION_HIS(P_PERSON_ID IN NUMBER)AS
 
  PRAGMA AUTONOMOUS_TRANSACTION;

  ln_person_id NUMBER;
  ln_lwd_date per_people_extra_info.pei_information3%TYPE := NULL;
  lc_TERMI_ACCEPTED_PERSON_ID per_people_extra_info.pei_information1%TYPE;
  lc_ACCEPTED_TERMINATION_DATE per_people_extra_info.pei_information2%TYPE;
  lc_ACTUAL_TERMINATION_DATE per_people_extra_info.pei_information3%TYPE;
  lc_FINAL_PROCESS_DATE per_people_extra_info.pei_information4%TYPE;
  lc_LAST_STANDARD_PROCESS_DATE per_people_extra_info.pei_information5%TYPE;
  lc_LEAVING_REASON per_people_extra_info.pei_information6%TYPE;
  lc_NOTIFIED_TERMINATION_DATE per_people_extra_info.pei_information7%TYPE;
  lc_PROJECTED_TERMINATION_DATE per_people_extra_info.pei_information8%TYPE;
  lc_ATTRIBUTE1 per_people_extra_info.pei_information9%TYPE;
  lc_ATTRIBUTE2 per_people_extra_info.pei_information10%TYPE;
  lc_ATTRIBUTE3 per_people_extra_info.pei_information17%TYPE;
  lc_ATTRIBUTE4 per_people_extra_info.pei_information11%TYPE;
  lc_ATTRIBUTE5 per_people_extra_info.pei_information12%TYPE;
  lc_ATTRIBUTE6 per_people_extra_info.pei_information13%TYPE;
  lc_ATTRIBUTE7 per_people_extra_info.pei_information14%TYPE;
  lc_ATTRIBUTE8 per_people_extra_info.pei_information15%TYPE;
  lc_ATTRIBUTE9 per_people_extra_info.pei_information16%TYPE;
 
  lc_information_type per_people_extra_info.information_type%TYPE :=  'EIT_RESIGNATION_HISTORY';
  lc_pei_information_category  per_people_extra_info.pei_information_category%TYPE := 'EIT_RESIGNATION_HISTORY';
  ln_person_extra_info_id per_people_extra_info.person_extra_info_id%TYPE :=  NULL;
  ln_object_version_number per_people_extra_info.object_version_number%TYPE := NULL;
  v_error VARCHAR2 (3000);
 
  CURSOR cur_pps_det(c_person_id IN NUMBER)
  IS
   
    SELECT
    PERSON_ID
    , TERMINATION_ACCEPTED_PERSON_ID
    , TO_CHAR(ACCEPTED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') ACCEPTED_TERMINATION_DATE
    , TO_CHAR(ACTUAL_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') ACTUAL_TERMINATION_DATE
    , TO_CHAR(FINAL_PROCESS_DATE,'RRRR/MM/DD HH24:MI:SS') FINAL_PROCESS_DATE
    , TO_CHAR(LAST_STANDARD_PROCESS_DATE,'RRRR/MM/DD HH24:MI:SS') LAST_STANDARD_PROCESS_DATE
    , LEAVING_REASON
    , TO_CHAR(NOTIFIED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') NOTIFIED_TERMINATION_DATE
    , TO_CHAR(PROJECTED_TERMINATION_DATE,'RRRR/MM/DD HH24:MI:SS') PROJECTED_TERMINATION_DATE
    , ATTRIBUTE1
    , ATTRIBUTE2
    , ATTRIBUTE3
    , ATTRIBUTE4
    , ATTRIBUTE5
    , ATTRIBUTE6
    , ATTRIBUTE7
    , ATTRIBUTE8
    , ATTRIBUTE9
    FROM
      per_periods_of_service
    WHERE
      PERSON_ID = c_person_id
      AND actual_termination_date is not null;
BEGIN
 
 
  FOR i IN cur_pps_det(p_person_id)
  LOOP
    --DBMS_OUTPUT.put_line ('After Cursor');
    BEGIN
      ln_person_id                      := i.PERSON_ID;
      lc_TERMI_ACCEPTED_PERSON_ID       := i.TERMINATION_ACCEPTED_PERSON_ID;
      lc_ACCEPTED_TERMINATION_DATE      := i.ACCEPTED_TERMINATION_DATE;
      lc_ACTUAL_TERMINATION_DATE        := i.ACTUAL_TERMINATION_DATE;
      lc_FINAL_PROCESS_DATE             := i.FINAL_PROCESS_DATE;
      lc_LAST_STANDARD_PROCESS_DATE     := i.LAST_STANDARD_PROCESS_DATE;
      lc_LEAVING_REASON                 := i.LEAVING_REASON;
      lc_NOTIFIED_TERMINATION_DATE      := i.NOTIFIED_TERMINATION_DATE;
      lc_PROJECTED_TERMINATION_DATE     := i.PROJECTED_TERMINATION_DATE;
      lc_ATTRIBUTE1                     := i.ATTRIBUTE1;
      lc_ATTRIBUTE2                     := i.ATTRIBUTE2;
      lc_ATTRIBUTE3                     := i.ATTRIBUTE3;
      lc_ATTRIBUTE4                     := i.ATTRIBUTE4;
      lc_ATTRIBUTE5                     := i.ATTRIBUTE5;
      lc_ATTRIBUTE6                     := i.ATTRIBUTE6;
      lc_ATTRIBUTE7                     := i.ATTRIBUTE7;
      lc_ATTRIBUTE8                     := i.ATTRIBUTE8;
      lc_ATTRIBUTE9                     := i.ATTRIBUTE9;
     
         
      hr_person_extra_info_api.create_person_extra_info (p_validate => FALSE,
      p_person_id                 => ln_person_id,
      p_information_type          => lc_information_type,
      p_pei_information_category  => lc_pei_information_category,
      p_pei_information1          => lc_TERMI_ACCEPTED_PERSON_ID,
      p_pei_information2          => lc_ACCEPTED_TERMINATION_DATE,
      p_pei_information3          => lc_ACTUAL_TERMINATION_DATE,
      p_pei_information4          => lc_FINAL_PROCESS_DATE,
      p_pei_information5          => lc_LAST_STANDARD_PROCESS_DATE,
      p_pei_information6          => lc_LEAVING_REASON,
      p_pei_information7          => lc_NOTIFIED_TERMINATION_DATE,
      p_pei_information8          => lc_PROJECTED_TERMINATION_DATE,
      p_pei_information9          => lc_ATTRIBUTE1,
      p_pei_information10         => lc_ATTRIBUTE2,
      p_pei_information11         => lc_ATTRIBUTE4,
      p_pei_information12         => lc_ATTRIBUTE5,
      p_pei_information13         => lc_ATTRIBUTE6,
      p_pei_information14         => lc_ATTRIBUTE7,
      p_pei_information15         => lc_ATTRIBUTE8,
      p_pei_information16         => lc_ATTRIBUTE9,
      p_pei_information17         => lc_ATTRIBUTE3,
      p_person_extra_info_id      => ln_person_extra_info_id,
      p_object_version_number     => ln_object_version_number );
      COMMIT;
      --DBMS_OUTPUT.put_line ('After API');
 
 
    EXCEPTION
   
    WHEN OTHERS THEN
      v_error := 'Loc API :' || ln_person_id || SQLERRM;
      --DBMS_OUTPUT.put_line (v_error);
    END;
 
  END LOOP;

EXCEPTION

WHEN OTHERS THEN
  v_error := SQLERRM;
  --DBMS_OUTPUT.put_line (v_error);
END XX_EMP_RESIGNATION_HIS;