1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Issue with Creating employee API ORA-01403: no data found.

Discussion in 'Oracle HRMS & Payroll' started by bazthejockey, Jun 18, 2014.

  1. bazthejockey

    bazthejockey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    75
    Hi everyone,

    Im trying to learn how to use API's and as a test I came accross the below API script but it doesnt appear to have created the test employee??

    I get the error message ORA-01403: no data found.



    here is the code;

    Code (Text):

    DECLARE
     lc_employee_number                     PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE;
     ln_person_id                           PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
     ln_assignment_id                       PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
     ln_object_ver_number                   PER_ALL_ASSIGNMENTS_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                 PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
     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;
     
    BEGIN
               hr_employee_api.create_employee
               (   -- Input data elements  
                   -- ------------------------------
                   p_hire_date                              =>  TRUNC(SYSDATE),
                   p_business_group_id                      => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
                   p_last_name                              => 'Doe',
                   p_sex                                    => 'M',
                   p_first_name                             => 'Jane',
                   p_middle_names                           => NULL,
                   p_national_identifier                    => 'JS123453K',
                   p_date_of_birth                          => trunc(TO_DATE('01-JAN-1980')),
                   p_known_as                               => 'PRAJ',  
                   -- Output data elements  
                   -- --------------------------------
                   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);
     
    EXCEPTION
          WHEN OTHERS THEN
                        ROLLBACK;
                        dbms_output.put_line(SQLERRM);

    COMMIT;
    END;
    /

    SHOW ERR;
     
    Any help on this would be great.

    Thanks

    Barry
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    fnd_profile.value_specific('PER_BUSINESS_GROUP_ID') - it is executed without errors?
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Barry,

    I just tried your script and it works fine on my test instance, which is an EBS R12.1.3 Vision instance.

    If I recall from one of your previous posts, you're on R11. Unfortunately, I don't have such an instance to check.

    From your side, could you verify (from the article you picked up this script) if it's for R11 or supposed to work on both.

    What's your instance like - clean instance (freshly installed) or Vision ? Suppose you're running script connected as "apps" user.

    If it's a clean instance, Has HR been setup ? By that I mean, have the basic setups been done. Before you can create employees, there are a certain number of setups which need to be done (refer to Oracle HCM Implementation Guide).
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    I ran same script in R12, It shows ORA-20001: Employee number is required. Please enter a valid value.

    So I gave one unique number for lc_employee_number then record created in hr_employees table.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    That's exactly the point: in your EBS environment Jagadekara, the employee number generation at the BG level must have been set to "Manual", i.e., you need to enter the Employee Number, it's not generated by the system (like in my Vision instance).

    You may have other defaulted attributes or controls/restrictions in your environment. Try to check with your HR Functional team and also see if you can create the employee using the screen with these minimum information from your script and note any items or fields required or defaulted in the Form and try to include same in the arguments of the call to "create_employee" procedure.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Yes, That's right...
     
  7. bazthejockey

    bazthejockey Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    75
    Hi Rajen,

    You are right, I also found issues as we do not have payroll module with our organisation and I also found that I also needed to use the UK version not the american version for our build.

    The final working code for creating employees in our UK HRMS is;

    Code (SQL):

     
     ALTER SESSION SET NLS_LANGUAGE=AMERICAN NLS_TERRITORY=America;
    DECLARE
      x_emp_num VARCHAR2(200);
      x_business_group_id      NUMBER;
      x_person_type_id         NUMBER;
      x_validate_mode             BOOLEAN := FALSE;  
      x_person_id                 INTEGER;
      x_assignment_id             INTEGER;
      x_per_object_version_number NUMBER;
      x_asg_object_version_number NUMBER;
      x_per_effective_start_date  DATE;
      x_per_effective_end_date    DATE;
      x_full_name                 VARCHAR2(300);
      x_per_comment_id            NUMBER;
      x_assignment_sequence       NUMBER;
      x_assignment_number         VARCHAR2(10);
      x_name_combination_warning  BOOLEAN := FALSE;
      x_assign_payroll_warning    BOOLEAN := FALSE;
      x_orig_hire_warning         BOOLEAN := FALSE;
    BEGIN
    --get business_group_id and person_id
      SELECT haou.business_group_id, ppt.person_type_id
        INTO x_business_group_id, x_person_type_id
        FROM hr.HR_ALL_ORGANIZATION_UNITS haou, per_person_types ppt
        WHERE haou.name = '!My Organisation name!'
        AND ppt.user_person_type = 'Current Employee'
        AND ppt.business_group_id = haou.business_group_id;
      --DBMS_OUTPUT.PUT_LINE(x_business_group_id || x_person_type_id);

    hr_employee_api.create_gb_employee(p_validate                  => x_validate_mode
                                        ,p_hire_date                 => SYSDATE -- In this case
                                        ,p_business_group_id         => x_business_group_id
                                        ,p_last_name                 => 'Bloggs'
                                        ,p_sex                       => 'M'
                                        ,p_person_type_id            => x_person_type_id
                                        ,p_date_of_birth             => '12-JAN-1972'
                                        ,p_employee_number           => x_emp_num
                                        ,p_first_name                => 'Joe'
                                        ,p_known_as                  => ''
                                        ,p_marital_status            => ''
                                        ,p_middle_names              => ''
                                        ,p_ni_number                 => 'PX474579D'
                                        ,p_previous_last_name        => ''
                                        ,p_title                     => 'MR.'
                                        ,p_original_date_of_hire     => SYSDATE
                                        ,p_person_id                 => x_person_id
                                        ,p_assignment_id             => x_assignment_id
                                        ,p_per_object_version_number => x_per_object_version_number
                                        ,p_asg_object_version_number => x_asg_object_version_number
                                        ,p_per_effective_start_date  => x_per_effective_start_date
                                        ,p_per_effective_end_date    => x_per_effective_end_date
                                        ,p_full_name                 => x_full_name
                                        ,p_per_comment_id            => x_per_comment_id
                                        ,p_assignment_sequence       => x_assignment_sequence
                                        ,p_assignment_number         => x_assignment_number
                                       -- ,p_PER_INFORMATION1          => ''!MY SIT CODE for ethical group!'
                                        ,p_ethnic_origin             => '!MY SIT CODE for ethical group!'
                                        ,p_name_combination_warning  => x_name_combination_warning
                                        ,p_assign_payroll_warning    => x_assign_payroll_warning
                                        ,p_orig_hire_warning         => x_orig_hire_warning);
    COMMIT ;
    END;
    SELECT * FROM per_all_people_f
    WHERE last_name = 'Bloggs';
     
    Thank you all for your help.

    Kind regards,
    Barry