Create Employee Using API

Overview:

   This document help you to create Employee in Oracle Apps.

Follow below steps to create employee using API.

1) Create staging table.
2) Create Control file (.ctl).
3) Create or get comma separated (.csv) file.
4) Load control file and comma separated file on Server.
5) Create Concurrent Executable and Concurrent Program for data inserting in staging table.
6) Create validation package or procedure.
7) Create Concurrent Executable and Concurrent Program for validation package.

(1) Create staging table:

    We need to create staging table to insert records.
    For example:
        CREATE TABLE XXEMP_STG(first_name VARCHAR2(100) 
                      ,last_name          VARCHAR2(100)
                      ,gender             VARCHAR2(5) 
                      ,date_of_birth      DATE
                      ,email_address      VARCHAR2(100)
                      ,person_type        VARCHAR2(80)
                      ,hire_date          DATE
                      ,created_by         NUMBER
                      ,creation_date      DATE
                      ,last_updated_by    NUMBER
                      ,last_update_login  NUMBER
                      ,request_id         NUMBER
                      ,record_status      VARCHAR2(10)
                      ,error_message      VARCHAR2(2000),
       CONSTRAINT xxemp_pk PRIMARY KEY (request_id)
                      );

(2) Create Control file (.ctl):

    OPTIONS (ERRORS=10000,SKIP=1)
    LOAD DATA
    REPLACE
    INTO TABLE XXEMP_STG
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
        (   first_name
            ,last_name
           ,gender
            ,date_of_birth
            ,email_address
           ,person_type
            ,hire_date
           ,record_status
            ,request_id
        )

(3) Create or get comma separated (.csv) file:

    first_name,last_name,gender,date_of_birth,email_address,person_type,hire_date,request_id
    "Raj","Sharma","Male","05-SEP-1977","Rajsharma@gmail.com","Employee","01-JUL-2014",100
--
--

(4) Load control file and comma separated file on Server using WinSCP.

Control file put on /BIN folder and comma separated (.csv) file put on custom path, we use comma separated file path as a parameter for load or insert records in staging table.

(5) Create Concurrent Executable and Concurrent Program for data insert in staging table.

    (A) Create Executable:

        ---------------------------------
                        Executable : XXEMP_LOAD
                      Short Name : XXEMP007
                       Application : XX Custom top
                       Description : Load employee data in staging table
            Execution Method : SQL*Loader
        Execution File Name : XXEMP007     -- This is control file name (.ctl)
              Subroutine Name :
           Execution File Path :
        ---------------------------------

    (B) Create Concurrent Program:

        ---------------------------------
                       Program : XXEMP - Load data in staging table
                  Short Name : XXEMP007
                  Application : XX Custom top
                  Description : Load employee data in staging table
           Executable
                            Name : XXEMP007
                         Method : SQL*Loader
              Output Format : Text
        ---------------------------------
        Parameters: 1) Data File with Path    -- this is Required parameter. Put file name with path (e.g./gh01/DEV/fs1/EBSapps/appl/Custom/12.0.0/bin/XXEMP_007.csv)
  ** XXEMP_007.csv  -- is comma separated file name.

   * Attach above concurrent program to particular request group and run "XXEMP - Load data in staging table" program.
         Once program completed normally it means data inserted in Staging table.
Check in staging table: SELECT * FROM XXEMP_STG;

(6) Create validation package or procedure:

    ** Package Specification:

 ----------------------------------------------
  CREATE OR REPLACE PACKAGE BODY XXEMP_API_PKG
  AS
            PROCEDURE create_emp( errbuf OUT VARCHAR2
                                 ,retcode OUT NUMBER
                                );
          END XXEMP_API_PKG;
          ----------------------------------------------

    ** Package Body:

 
      CREATE OR REPLACE PACKAGE BODY XXEMP_API_PKG
      AS

      PROCEDURE create_emp( errbuf OUT VARCHAR2
                           ,retcode OUT NUMBER
                          )
      AS
       --===============================================================+
       -- | Name :              create_emp
       -- | Description :     The procedure From the staging table.
       -- |
       -- | Parameters : errbuf             OUT NOCOPY     VARCHAR2
       -- |                      retcode            OUT NOCOPY      NUMBER
       -- ===============================================================+
       --  Declaring the cursor to get all NEW records to create employee from staging table.
 
    CURSOR lcur_employee_data
    IS
      SELECT  estg.*
      FROM XXEMP_STG estg
      WHERE UPPER(estg.record_status) = 'NEW';
  --
      --  Declaring of local variables.
      ln_person_id                   per_all_people_f.person_id%TYPE;
      ln_assignment_id               NUMBER;
      ln_object_ver_number           per_all_people_f.object_version_number%TYPE;
      ln_asg_ovn                     NUMBER;
      ld_per_effective_start_date    per_all_people_f.effective_start_date%TYPE;
      ld_per_effective_end_date      per_all_people_f.effective_end_date%TYPE;
      lc_full_name                   per_all_people_f.full_name%TYPE;
      ln_per_comment_id              per_all_people_f.comment_id%TYPE;
      ln_assignment_sequence         NUMBER;
      lc_assignment_number           per_all_assignments_f.assignment_number%TYPE;
      lb_name_combination_warning    BOOLEAN;
      lb_assign_payroll_warning      BOOLEAN;
      lb_orig_hire_warning           BOOLEAN;
  --
  ln_business_group_id           NUMBER := 107;
  --* business_group_id is Your own business id, Select this from "Per_business_groups" table.
  ln_person_type_id              NUMBER;
  lc_employee_number             VARCHAR2(100);
  lc_gender                      VARCHAR2(5);
  lc_rec_status                  VARCHAR2(10):= 'S';
      --
  BEGIN
    FOR lreq_emp IN lcur_employee_data
    LOOP
  -- Variable declaration
  ln_person_type_id := NULL;
  lc_employee_number := NULL;
  lc_gender  := NULL;
--
--Start validation
--1) Last_name
  IF (lreq_emp.last_name IS NULL) THEN
        fnd_file.put_line(fnd_file,'Enter Last Name.');
lc_rec_status := 'E';
  END IF;
 
        --2) Person Type
  IF (lreq_emp.person_type IS NULL) THEN
        fnd_file.put_line(fnd_file,'Enter person type.');
lc_rec_status := 'E';
  ELSE
      BEGIN
          SELECT person_type_id INTO ln_person_type_id
  FROM per_person_types
                  WHERE user_person_type = lreq_emp.person_type
                        AND business_group_id = ln_business_group_id;
              EXCEPTION
  WHEN no_data_found THEN
      fnd_file.put_line(fnd_file,'Entered person type not found.');
      lc_rec_status := 'E';
  WHEN others THEN
             fnd_file.put_line(fnd_file,'Error at person type: '||SQLERRM);
     lc_rec_status := 'E';
      END;
  END IF;

        --3) Gender
    BEGIN
       SELECT lookup_code INTO lc_gender
               FROM hr_lookups
               WHERE UPPER(meaning) = UPPER(lreq_emp.gender)
                     AND hl.lookup_type = 'SEX';
          EXCEPTION
      WHEN no_data_found THEN
         fnd_file.put_line(fnd_file,'Entered Gender is not found.');
         lc_rec_status := 'E';
     WHEN others THEN
 fnd_file.put_line(fnd_file,'Error at Gender:'||SQLERRM);
         lc_rec_status := 'E';
  END;
    --
    -- Call API to create Employee
IF (lc_rec_status = 'S') THEN
        hr_employee_api.create_employee( p_validate                 => FALSE
                                    ,p_business_group_id          => ln_business_group_id
                                    ,p_hire_date                  => lr_emp.hire_date
                                    ,p_last_name                  => lr_emp.last_name
                                    ,p_sex                        => lr_emp.gender
                                    ,p_person_type_id             => ln_person_type_id
                                    ,p_date_of_birth              => lr_emp.date_of_birth
                                    ,p_first_name                 => lr_emp.first_name
                                    ,p_national_identifier        => '122-22-9876'
                                    ,p_employee_number            => lc_employee_number
                                    ,p_person_id                  => ln_person_id
                                    ,p_assignment_id              => ln_assignment_id
                                    ,p_per_object_version_number  => ln_object_ver_number
                                    ,p_asg_object_version_number  => ln_asg_ovn
                                    ,p_per_effective_start_date   => ld_per_effective_start_date
                                    ,p_per_effective_end_date     => ld_per_effective_end_date
                                    ,p_full_name                  => lc_full_name
                                    ,p_per_comment_id             => ln_per_comment_id
                                    ,p_assignment_sequence        => ln_assignment_sequence
                                    ,p_assignment_number          => lc_assignment_number
                                    ,p_name_combination_warning   => lb_name_combination_warning
                                    ,p_assign_payroll_warning     => lb_assign_payroll_warning
                                    ,p_orig_hire_warning          => lb_orig_hire_warning
                                    );

     fnd_file.put_line(fnd_file.LOG,'Person_id:-'||ln_person_id);                               
     fnd_file.put_line(fnd_file.log,'Employee_Number:-'||lc_employee_number);
    END IF;
--
-- Update staging table
  BEGIN
    UPDATE XXEMP_STG estg
            SET record_status = lc_rec_status
            WHERE UPPER(estg.record_status) = 'NEW'
    AND request_id = lreq_emp.request_id;
  END;
    -- 
    END LOOP;--------------------------lr_emp loop end
    COMMIT;
      errbuf := 'Staging table records successfully inserted in Base table(PER_ALL_PEOPLE_F)';
      retcode := gn_success;
      EXCEPTION
        WHEN OTHERS THEN
          ROLLBACK;
          retcode := gn_warning;
          errbuf := SUBSTR(SQLERRM,1,200);
  END create_emp;
END XXEMP_API_PKG;

Note: Compile this package with valid status.

(7) Create Concurrent Executable and Concurrent Program for validation package:

    (A) Create Executable:

        ---------------------------------
                 Executable : XXEMP_IMPORT
                 Short Name : XXEMP008
                Application : XX Custom top
                Description : Load employee data in base table
           Execution Method : PL/SQL Stored Procedure
        Execution File Name : XXEMP_API_PKG.create_emp     -- This is validation package.procedure name
            Subroutine Name :
        Execution File Path :
        ---------------------------------

    (B) Create Concurrent Program:

        ---------------------------------
                    Program : XXEMP - Load data in base table
                 Short Name : XXEMP008
                Application : XX Custom top
                Description : Load employee data in base table
           Executable
                       Name : XXEMP008
                     Method : PL/SQL Stored Procedure
              Output Format : Text
        ---------------------------------

   * Attach above concurrent program to particular request group and run "XXEMP - Load data in base table" program.
     Once program completed normally it means data inserted in Base table.
Check in base table: SELECT * FROM per_all_people_f WHERE first_name = 'Raj';