Oracle EBS R12 Update Employee Details

 DECLARE

  --In Variables for Update Employee API

  l_employee_number            per_all_people_f.employee_number%TYPE;-- := 'XX1635';

  l_object_version_number      per_all_people_f.object_version_number%TYPE;

  l_person_id                  per_all_people_f.person_id%TYPE;

  l_dt_ud_mode                 VARCHAR2 (200) := 'CORRECTION';

  l_effective_date             DATE := trunc ( SYSDATE);

  --l_marital_status             per_all_people_f.marital_status%TYPE := :p_marital_status;

  --l_suffix                     per_all_people_f.marital_status%TYPE := :p_suffix;

  -- Out Variables for Update Employee API

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

  o_effective_start_date       DATE;

  o_effective_end_date         DATE;

  o_full_name                  per_all_people_f.full_name%TYPE;

  o_comment_id                 per_all_people_f.comment_id%TYPE;

  o_name_combination_warning   BOOLEAN;

  o_assign_payroll_warning     BOOLEAN;

  o_orig_hire_warning          BOOLEAN;

  

  cursor c1 is 

   SELECT PPX.PERSON_ID, EMP_NUMBER , OTHERS1 FROM  XXFUJ_UPLOAD_TICKET XXFUJ, PER_PEOPLE_X PPX

WHERE 

EMPLOYEE_NUMBER = EMP_NUMBER

--AND EMP_NUMBER IN ( 'xx1635')-- and person_id is not null;

;

BEGIN

  -- Get person_id and object_version_number for employee_number

  for i in c1 loop

  BEGIN

    --

    SELECT person_id, object_version_number, employee_number

    INTO   l_person_id, l_object_version_number,l_employee_number

    FROM   per_all_people_f

    WHERE  employee_number = i.emp_number

    AND    sysdate BETWEEN effective_start_date AND effective_end_date

    AND    business_group_id = 81;

  --

  EXCEPTION

    WHEN OTHERS THEN

      NULL;

  END;

  --

  

  --

  -- Issue a savepoint.

  --

  SAVEPOINT update_person;

  --

  hr_person_api.update_person ( -- Input Data Elements

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

                               p_effective_date             => l_effective_date

                              ,p_datetrack_update_mode      => l_dt_ud_mode

                              ,p_person_id                  => l_person_id

                              ,p_per_information3                    => i.OTHERS1

                              --,p_marital_status             => l_marital_status

                              --,p_suffix                     => l_suffix

                              , -- Output Data Elements

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

                               p_employee_number            => l_employee_number

                              ,p_object_version_number      => l_object_version_number

                              ,p_effective_start_date       => o_effective_start_date

                              ,p_effective_end_date         => o_effective_end_date

                              ,p_full_name                  => o_full_name

                              ,p_comment_id                 => o_comment_id

                              ,p_name_combination_warning   => o_name_combination_warning

                              ,p_assign_payroll_warning     => o_assign_payroll_warning

                              ,p_orig_hire_warning          => o_orig_hire_warning);

  --

  COMMIT;

  dbms_output.put_line ('Employee # ' || l_employee_number || ' has been updated successfully!');

--

end loop;

EXCEPTION

  --

  WHEN OTHERS THEN

    ROLLBACK TO update_person;

    dbms_output.put_line (sqlerrm);

  --

END;

FND_USER_PKG.UPDATEUSER

Oracle EBS R12 Create new SIT (uploading SIT for employee)

DECLARE

CURSOR get_details

      IS

        

   SELECT TICKET_CATEGORY,TICKET_TYPE,DURATION,BALANCE, PPX.PERSON_ID, EMP_NUMBER FROM  XXFUJ_UPLOAD_TICKET XXFUJ, PER_PEOPLE_X PPX

WHERE 

EMPLOYEE_NUMBER = EMP_NUMBER

--AND EMP_NUMBER IN ( 'xx1635')-- and person_id is not null;

;

      l_err_msg                     VARCHAR2 (500) := NULL;

      l_id_flex_num                 NUMBER (9) := NULL;

      l_analysis_criteria_id        NUMBER (9) := NULL;

      l_person_analysis_id          NUMBER (9) := NULL;

      l_pea_object_version_number   NUMBER (9) := NULL;

   BEGIN

      --Initialize Session

      fnd_global.

       apps_initialize (user_id => 2605,                     -- XXXX_MIGRATION

                                        resp_id => 50419, -- XXXX HRMS Manager

                                                         resp_appl_id => 800 -- Human Resouces

                                                          );

      SELECT fifs.id_flex_num

        INTO l_id_flex_num

        FROM fnd_id_flex_structures fifs

       WHERE id_flex_structure_code LIKE 'FUJ_XX_DET';

      FOR fetch_details IN get_details

      LOOP

         l_err_msg := NULL;

         l_analysis_criteria_id := NULL;

         l_person_analysis_id := NULL;

         l_pea_object_version_number := NULL;

         BEGIN

            hr_sit_api.

             create_sit (

               p_validate                    => FALSE,

               p_person_id                   => fetch_details.person_id,

               p_business_group_id           => 81,

               p_id_flex_num                 => l_id_flex_num,

               p_effective_date              => SYSDATE,

               p_comments                    => NULL,

               p_date_from                   => SYSDATE

               --p_date_to                     => fetch_details.end_date,

               ,p_segment1                    => fetch_details.TICKET_CATEGORY

                            ,p_segment2                    => fetch_details.TICKET_TYPE

                            ,p_segment3                    => fetch_details.DURATION

                            ,p_segment4                    => nvl(fetch_details.BALANCE,30)

               ,p_analysis_criteria_id        => l_analysis_criteria_id,

               p_person_analysis_id          => l_person_analysis_id,

               p_pea_object_version_number   => l_pea_object_version_number);

           

            COMMIT;

         EXCEPTION

            WHEN OTHERS

            THEN

               l_err_msg := SQLERRM;

              DBMS_OUTPUT.put_line (' Exception: ' || l_err_msg);

               COMMIT;

         END;

      END LOOP;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_err_msg := SQLERRM;

         DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

   END ;

Oracle EBS R12 Delete Employee

1.

 update apps.fnd_user set employee_id=null

 WHERE employee_id IN 

 (SELECT DISTINCT person_id

                         FROM per_assignments_x

                        WHERE payroll_id = 149

                        and person_id <> 11694)

                        ;

--2. 

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, assignment_number EMPLOYEE_number

FROM per_assignments_x where payroll_id = 199

--and person_id = 11694

;

--–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

);

dbms_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;

ORACLE APPS R12 create Employee Address

 TABLE: XX_CREATE_EMP_ADDRESS

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';

  v_api_error   VARCHAR2(2000);

  CURSOR C1 IS

  

  SELECT *

    --INTO lrec_person_details

    FROM per_all_people_f papf

    , XX_CREATE_EMP_ADDRESS XXFIA

   WHERE  current_employee_flag = 'Y'

     AND 'FIA'||XXFIA.EMP_NUMBER = PAPF.EMPLOYEE_NUMBER

     AND PAPF.EMPLOYEE_NUMBER <> 'XX1442'

     AND SYSDATE BETWEEN effective_start_date AND effective_end_date; 

     

  

BEGIN 

FOR lrec_person_details IN C1 LOOP

  

     --SELECT * FROM PER_ADDRESSES WHERE PERSON_ID = 47856;

     

  BEGIN

  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                 => 'AE',

         p_date_from             => lrec_person_details.effective_start_date, 

         --p_address_line1         => '11, TEST ADDRESS LINE',

         --p_address_line2         => NULL,

         p_address_line3         => lrec_person_details.ADDRES_LINE3,

         p_country               => 'AE',

         --p_postal_code           => '40400',

         --p_region_1              => 'SELANGOR',

         P_TOWN_OR_CITY          => lrec_person_details.TOWN_OR_CITY,

         p_address_id            => ln_address_id,

         p_object_version_number => ln_object_version_num

        );

   DBMS_OUTPUT.PUT_LINE('Address id: '||ln_address_id); 

  COMMIT;

   UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'done', MESSAGE = NULL

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   EXCEPTION WHEN OTHERS THEN

   v_api_error   := sqlerrm;

     UPDATE  XX_CREATE_EMP_ADDRESS

   SET STATUS = 'Error', Message = v_api_error

   WHERE 'FIA'||EMP_NUMBER = lrec_person_details.EMPLOYEE_NUMBER

   ;

   END;

   END LOOP;

   EXCEPTION WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

       

API Apps R12 Document of Records

DECLARE

   l_document_extra_info_id   NUMBER := NULL;

   l_object_version_number    NUMBER := NULL;

   CURSOR C1 IS

   

   SELECT PPX.PERSON_ID,PPX.PER_INFORMATION18 NATIONALITY_CODE, 

   --fnd_conc_date.string_to_date(VALID_FROM)fnd_dt,

   to_date(VALID_FROM,'mm/dd/yyyy')fnd_dt,

   --FND_DATE.canonical_to_date(valid_from)fnd_dt,

   XXFUJ.* FROM XXFUJ_DOC_RECORDS_P XXFUJ, PER_PEOPLE_X PPX

   WHERE EMP_NUMBER = 'XX1085'

   AND EMP_NUMBER = EMPLOYEE_NUMBER

   AND PASSPORT_NUMBER IS NOT NULL

   and PPX.PER_INFORMATION18 is not null

   AND XXFUJ.EMP_NUMBER NOT IN 

   (

select PER.EMPLOYEE_NUMBER

FROM

    hr_document_extra_info dei,

    per_all_people_f per,

    hr_document_types hdt

WHERE

    per.person_id = dei.person_id

    AND   per.business_group_id = 81

    --AND   per.employee_number ='XX1085'

    AND   per.current_employee_flag = 'Y'

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

    AND   hdt.document_type_id = dei.document_type_id

    AND DEI_INFORMATION_CATEGORY = 'Passport'   

)

   ;

   

   

   

BEGIN

FOR I IN C1 LOOP

   hr_document_extra_info_api.create_doc_extra_info (

      p_validate                 => FALSE,

      p_person_id                => I.PERSON_ID,

      p_verified_by              => 0,

      p_document_type_id         => 62,

      p_date_from                => TO_DATE(I.VALID_FROM,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_FROM),

      p_date_to                  => TO_DATE(I.VALID_TO,'mm/dd/yyyy'),--fnd_conc_date.string_to_date(I.VALID_TO),

      p_document_number          => '',

      p_DEI_INFORMATION_CATEGORY => 'Passport',

      p_DEI_INFORMATION1       => I.PASSPORT_NUMBER,

      p_DEI_INFORMATION2       => 1,--NORMAL PASSPORT

      p_DEI_INFORMATION3       => I.NATIONALITY_CODE,

      p_DEI_INFORMATION6       => I.COUNTRY_ORIGIN,

      p_DEI_INFORMATION7       => i.UNIFIED_NUMBER, 

      --p_DEI_INFORMATION10       => I.LABOUR_CARD,

      ---p_DEI_INFORMATION11       =>TO_CHAR(I.LABOUR_CARD_DATE,'YYYY/MM/DD HH24:MI:SS'),--fnd_conc_date.string_to_date(I.LABOUR_CARD_DATE),

      p_document_extra_info_id   => l_document_extra_info_id,

      p_object_version_number    => l_object_version_number

   );

   COMMIT;

   DBMS_OUTPUT.put_line ('SUCCESS');

END LOOP;   

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('FAILURE ' || SQLERRM);

END;