Oracle HRMS API – Create Employee Address

DECLARE
    ln_address_id                           PER_ADDRESSES.ADDRESS_ID%TYPE;
    ln_object_version_number    PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE;

BEGIN

   -- Create Employee Address
   -- --------------------------------------
    hr_person_address_api.create_person_address
    (     -- Input data elements
          -- ------------------------------
          p_effective_date                    => TO_DATE('04-APR-2011'),
          p_person_id                           => 32979,
          p_primary_flag                     => 'Y',
          p_style                                     => 'US',
          p_date_from                           => TO_DATE('08-JUN-2011'),
          p_address_line1                   => '408 Main Street',
          p_address_line2                   => NULL,
          p_town_or_city                     => 'White Plains',
          p_region_1                              => 'Westchester',
          p_region_2                              => 'NY',
          p_postal_code                        => 10601,
          p_country                                => 'US',
          -- Output data elements
          -- --------------------------------
          p_address_id                          => ln_address_id,
          p_object_version_number   => ln_object_version_number
   );
 
 COMMIT;
EXCEPTION
       WHEN OTHERS THEN
                       ROLLBACK;
                       dbms_output.put_line(SQLERRM);
END;
/


Ur's
AmarAlam

Oracle HRMS API - Hire Into Job

DECLARE
     -- Local Variables
     -- ---------------------
     lc_dt_ud_mode             VARCHAR2(100)  := NULL;
     ln_person_id                 NUMBER               := 32981;
     ln_object_number        NUMBER               := 1;
     ld_effective_date            DATE                     := TO_DATE('04-APR-2012');
     lc_employee_number  VARCHAR2(100)  := 'CONTACT_AJ408';
 
     -- Out Variables for Find Date Track Mode API
     -- ------------------------------------------------------------
     lb_correction                          BOOLEAN;
     lb_update                               BOOLEAN;
     lb_update_override              BOOLEAN;
     lb_update_change_insert   BOOLEAN;
   
   -- Out Variables for Hire to Job API
   -- -------------------------------------------
   ld_effective_start_date           DATE;
   ld_effective_end_date            DATE;
   lb_assign_payroll_warning   BOOLEAN;
   lb_orig_hire_warning              BOOLEAN;
   ln_assignment_id                    NUMBER;

 BEGIN
   -- Find Date Track Mode
   -- ----------------------------
   dt_api.find_dt_upd_modes
   (   -- Input data elements
       -- ---------------------------
       p_effective_date                 => TO_DATE('04-APR-2012'),
       p_base_table_name         => 'PER_ALL_PEOPLE_F',
       p_base_key_column         => 'PERSON_ID',
       p_base_key_value             => ln_person_id,
       -- Output data elements
       -- -----------------------------
       p_correction                          => lb_correction,
       p_update                               => lb_update,
       p_update_override              => lb_update_override,
       p_update_change_insert  => lb_update_change_insert
   );
 
     IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       -- -----------------------------
       lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;

 

   IF ( lb_correction = TRUE )
   THEN
       -- CORRECTION
       -- --------------------
       lc_dt_ud_mode := 'CORRECTION';
   END IF;

   IF ( lb_update = TRUE )
   THEN
       -- UPDATE
       -- --------------
       lc_dt_ud_mode := 'UPDATE';
   END IF;
 
   -- Hire into Job API
   -- ------------------------
   hr_employee_api.hire_into_job
   (   -- Input Data Elements
       -- -----------------------------
       p_effective_date                     => ld_effective_date,
       p_person_id                           => ln_person_id,
       p_datetrack_update_mode  => lc_dt_ud_mode,
       -- Output Data Elements
       -- ----------------------------
       p_object_version_number    => ln_object_number,
       p_employee_number             => lc_employee_number,
       p_assignment_id                   => ln_assignment_id,
       p_effective_start_date           => ld_effective_start_date,
       p_effective_end_date            => ld_effective_end_date,
       p_assign_payroll_warning   => lb_assign_payroll_warning,
       p_orig_hire_warning             => lb_orig_hire_warning
   );
 
 COMMIT;

EXCEPTION
        WHEN OTHERS THEN
                       ROLLBACK;
                       DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/



Ur's
AmarAlam

Oracle HRMS API – Create Employee Contact

DECLARE
    ln_contact_rel_id                   PER_CONTACT_RELATIONSHIPS.CONTACT_RELATIONSHIP_ID%TYPE;
    ln_ctr_object_ver_num         PER_CONTACT_RELATIONSHIPS.OBJECT_VERSION_NUMBER%TYPE;
    ln_contact_person                 PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
    ln_object_version_number  PER_CONTACT_RELATIONSHIPS.OBJECT_VERSION_NUMBER%TYPE;
    ld_per_effective_start_date DATE;
    ld_per_effective_end_date  DATE;
    lc_full_name                            PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
    ln_per_comment_id              PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;
    lb_name_comb_warning     BOOLEAN;
    lb_orig_hire_warning           BOOLEAN;
 
BEGIN
    -- Create Employee Contact
    -- -------------------------------------
     hr_contact_rel_api.create_contact
     (    -- Input data elements
           -- -----------------------------
           p_start_date                                      => TO_DATE('04-APR-2011'),
           p_business_group_id                    => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
           p_person_id                                      => 32979,
           p_contact_type                                 => 'M',
           p_date_start                                      => TO_DATE('04-APR-2011'),
           p_last_name                                     => 'SIVA',
           p_first_name                                     => 'CONTACT',
           p_personal_flag                               => 'Y',
           -- Output data elements
           -- --------------------------------
          p_contact_relationship_id            => ln_contact_rel_id,
          p_ctr_object_version_number      => ln_ctr_object_ver_num,
          p_per_person_id                              => ln_contact_person,
          p_per_object_version_number     => ln_object_version_number,
          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_name_combination_warning  => lb_name_comb_warning,
          p_orig_hire_warning                      => lb_orig_hire_warning
     );
 
 COMMIT;

EXCEPTION
            WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;
/


Ur's
AmarAlam

Oracle HRMS API – Update Employee

DECLARE
   -- Local Variables
   -- -----------------------
   ln_object_version_number       PER_ALL_PEOPLE_F.OBJECT_VERSION_NUMBER%TYPE  := 7;
    lc_dt_ud_mode                            VARCHAR2(100)                                                                                     := NULL;
    ln_assignment_id                       PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE          := 33564;
    lc_employee_number                 PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE               := 'AJ408';
 
   -- Out Variables for Find Date Track Mode API
   -- ----------------------------------------------------------------
   lb_correction                                  BOOLEAN;
    lb_update                                        BOOLEAN;
    lb_update_override                      BOOLEAN;  
    lb_update_change_insert           BOOLEAN;

   -- Out Variables for Update Employee API
   -- -----------------------------------------------------------
    ld_effective_start_date                       DATE;
    ld_effective_end_date                        DATE;
    lc_full_name                                         PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
    ln_comment_id                                    PER_ALL_PEOPLE_F.COMMENT_ID%TYPE;  
    lb_name_combination_warning    BOOLEAN;
    lb_assign_payroll_warning             BOOLEAN;
    lb_orig_hire_warning                        BOOLEAN;


BEGIN


    -- Find Date Track Mode
    -- --------------------------------
    dt_api.find_dt_upd_modes
     (    -- Input Data Elements
          -- ------------------------------
          p_effective_date                           => TO_DATE('04-APR-2010'),
          p_base_table_name                    => 'PER_ALL_ASSIGNMENTS_F',
          p_base_key_column                   => 'ASSIGNMENT_ID',
          p_base_key_value                       => ln_assignment_id,
          -- Output data elements
          -- -------------------------------
         p_correction                                   => lb_correction,
         p_update                                         => lb_update,
         p_update_override                       => lb_update_override,
         p_update_change_insert            => lb_update_change_insert
   );
 
   IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
          -- UPDATE_OVERRIDE
          -- ---------------------------------
          lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;

   IF ( lb_correction = TRUE )
   THEN
         -- CORRECTION
         -- ----------------------
         lc_dt_ud_mode := 'CORRECTION';
   END IF;

   IF ( lb_update = TRUE )
   THEN
        -- UPDATE
        -- --------------
         lc_dt_ud_mode := 'UPDATE';
   END IF;
 
    -- Update Employee API
    -- ---------------------------------  
    hr_person_api.update_person
    (       -- Input Data Elements
            -- ------------------------------
            p_effective_date                              => TO_DATE('04-APR-2010'),
            p_datetrack_update_mode         => lc_dt_ud_mode,
            p_person_id                                     => 32979,
            p_middle_names                            => 'ALAM',
            p_marital_status                             => 'M',
            -- Output Data Elements
            -- ----------------------------------
           p_employee_number                       => lc_employee_number,
           p_object_version_number              => ln_object_version_number,
           p_effective_start_date                      => ld_effective_start_date,
           p_effective_end_date                       => ld_effective_end_date,
           p_full_name                                       => lc_full_name,
           p_comment_id                                   => ln_comment_id,
           p_name_combination_warning   => lb_name_combination_warning,
           p_assign_payroll_warning           => lb_assign_payroll_warning,
           p_orig_hire_warning                      => lb_orig_hire_warning
    );
 
   COMMIT;


EXCEPTION
       WHEN OTHERS THEN
                   ROLLBACK;
                   dbms_output.put_line(SQLERRM);
END;
/


Ur's
AmarAlam

Oracle HRMS API – Create Employee

API - hr_employee_api.create_employee

Example --

  -- Create Employee
 -- -------------------------

DECLARE

 
 lc_employee_number                       PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%TYPE    := 'AJ408';
 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                                         => TO_DATE('04-APR-2010'),
               p_business_group_id                      => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                                       => 'ALAM',
               p_first_name                                       => 'AMAR',
               p_middle_names                              => NULL,
               p_sex                                                     => 'M',
               p_national_identifier                       => '408-408-4080',
               p_date_of_birth                                 => TO_DATE('04-APR-1990'),
               p_known_as                                       => 'AJ',
               -- 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
        );
 
    COMMIT;



EXCEPTION
      WHEN OTHERS THEN
                    ROLLBACK;
                    dbms_output.put_line(SQLERRM);
END;
/


Ur's
AmarAlam

Oracle HRMS Interview Questions

What are the minimum classifications to create a Business Group?
(i) Business Group
(ii) GRE/Legal Entity
(iii) HR Organization

What are the Pre-Requisites for creating a Business Group?
(i) Value Sets
(ii) Key Flexfields
(iii) Location

What is the use of HR Organization?
If we want to assign the employee information to a Business Group then we need to have HR Organization
classification under a Business Group.

What is People Group?
(i) It is a Flexible area for holding user-defined assignment data.
(ii) Data can be used for Grouping sets of assignment together.
(iii) People group can be used for Element eligibility.
(iv) This information is used by the Payroll Run.
(v) The data will be held in PAY_PEOPLE_GROUPS.
(vi) GROUP_NAME field holds the concatenation of Segment data.

What is the KFF structure for SIT?
Personal Analysis Flexfields.

What is the DFF structure for EIT?
(i) Extra Person Information
(ii) Assignment Extra Information
(iii) Extra Location Information
(iv) Extra Position Information
(v) Extra Job Information
(vi) Organization Developer DF

How to enable EIT?
Switch Responsibility to ‘Human Resources, Vision Enterprises’
Double click on ‘Security’
Click on ‘Information Types’
Create your own ‘Information Types’ under your ‘Responsibility Name’.

How to enable SIT?
Switch Responsibility to ‘Human Resources, Vision Enterprises’
Double click on ‘Other Definitions’
Click on ‘Special Information Types’

What is the use of Date Track?
1) It is used to maintain the record history by creating a new record when the date track mode is UPDATE and
override on the existing record when the Data track mode is CORRECTION.
2) The value of the Data Track record depends on the date.
3) Tables ending with _F are date track tables.
4) To control data tracked rows, every Date Track table must include Effective_start_date & Effective_end_date.
5) The Effective_Start_Date indicates when the record inserted.
6) The Effective_End_Date indicates when the record updated or deleted.

What is the use of Object Version Number?
1) It is used to capture the latest record from the data base table.
2) When a row is inserted its number is set to 1.
3) If any updates performed on the row then the OVN is incremented.
4) Every API has the OVN parameter.
5) For create API this parameter is defined as an OUT parameter.
6) For update API this parameter is defined as an IN OUT parameter.
7) The APIs use it to check a row has been updated by another user, to prevent overwriting their changes.

What is the Element?
It is a Data Structure which is used to hold information for both Human Resources and Payroll.
In Human Resources elements may represents compensation types including Earnings such as Salary, Hourly Wages and Bonuses. In Payroll, elements constitute all the items in the Payroll run process.

What are the Classification Priorities?
(i) Information
(ii) Non-Payroll
(iii) Earnings
(iv) Pre-Tax Deductions
(v) Tax Deductions
(vi) Employer Tax
(vii) Voluntary Deductions
(viii) In Voluntary Deductions

What the Element can represent?
Earnings --> such as Salary, Wages & Bonuses
Benefits --> such as employee stock & pension plans
Non-Payroll items --> such as Expenses
Absences from work
Voluntary and In-Voluntary deductions
Employer Taxes and other Liabilities.

What are the pre-defined Elements?
UK Payroll legislation provides many predefined elements
--Tax
--National Insurance (Employee/Employer)
--Court Orders
These Elements cannot be modified.

What are the Element Entry Concepts?
Recurring à Recurring Entries can exists over many Payroll periods
Non-Recurring à Non-Recurring Entries are valid for single Payroll period only.

What are the types of Element Entry?
There are four types
Normal Entry
Override Entry
Additional Entry
Adjustment Entry
--Additive Adjustment
--Replacement Adjustment
--Balance Adjustment.

How can we add a new input value to an existing Element?
We can add an additional input values to an existing Element if the element has not been processed in a Payroll run
and the Effective data is the same date of creation of the Element.

What is the use of ID_FLEX_NUM?
It is used to define the Structure Definition.
The Structure Definition is held in FND_ID_FLEX_STRUCTURES
The Structure Segment Definition is held in FND_ID_FLEX_SEGMENTS.

What is the Element Link?
Links identify one or more assignment components that must be included in an employee's assignment for them to be eligible.Elements can, but they don’t have to, be linked by: Organization GroupJob PositionGrade
LocationEmployment Category (i.e., Fulltime-Regular, Part-time-Regular) Payroll Salary Basis.

What is API?
1. API is packaged procedure which can be used as an entry point into Application.
2.  The advantage of using an API is we can enter new information or alter the existing data without manual enters the information into the Application.
3.  APIs allow users to maintain HRMS information without using Oracle Application forms. How do i use an API to upload the data?
4.  API package contains many procedures to insert/update/delete the application data.
5. The API is not executed on its own, the API must be called or executed by other pl/sql program.
6. The API package should never be modified for custom use, if modified Oracle will not be able to support them.
7. None of the HRMS APIs issue commit, the calling module should manage the commit of the transaction.
How do i identify the Package name and version of the API?

SELECT text
FROM all_source
WHERE name like ‘HR_EMPLOYEE_API%’
AND text LIKE ‘%Header%’;


Ur's
AmarAlam

Oracle HRMS API's

HRMS API's
1. Updating the Per_periods_of_service table using
hr_ex_employee_api.update_term_details_emp

2. Terminating using
hr_ex_employee_api.actual_termination_emp

3. Applying the final process in the shared instance using
hr_ex_employee_api.final_process_emp

4. Re - Hire
hr_employee_api.re_hire_ex_employee

5. Updation On Already Existing Records
hr_person_api.update_us_person

6. New Hire
hr_employee_api.create_us_employee

7. Costing
pay_cost_allocation_api.create_cost_allocation

8. Load Update Assign
hr_assignment_api.update_us_emp_asg

9. Load Update Assign Criteria
hr_assignment_api.update_emp_asg_criteria

10. IF CONTACT PERSON ALREADY CREATED AND ONLY RELATION SHIP IS TO BE CREATED -
Note : Contact Person Id Is To Be Passed
hr_contact_rel_api.create_contact

11. If Contact Person Already Not Created
Note : Contact Person Id is passed as Null(default of API).
hr_contact_rel_api.create_contact

12. Load Phones
hr_phone_api.create_phone
hr_person_address_api.update_person_address

13. Load Addresses
hr_person_address_api.update_person_address
hr_person_address_api.create_person_address

14. Load Payment Methods
hr_personal_pay_method_api.create_us_personal_pay_method

15. Element Loading
py_element_entry_api.create_element_entry
py_element_entry_api.update_element_entry

16. Load Salaries
hr_upload_proposal_api.upload_salary_proposal

17. Approve Salary Proposal
hr_maintain_proposal_api.approve_salary_proposal

18. Starts To Validate/Load Federal Tax For A Person
pay_federal_tax_rule_api.update_fed_tax_rule

19. State tax rules
pay_state_tax_rule_api.create_state_tax_rule
pay_state_tax_rule_api.update_state_tax_rule

20. County Tax Rules
pay_county_tax_rule_api.create_county_tax_rule
pay_county_tax_rule_api.update_county_tax_rule

21. City Tax Rules
pay_city_tax_rule_api.create_city_tax_rule
pay_city_tax_rule_api.update_city_tax_rule

22. Schools and Colleges
per_esa_upd.upd
per_esa_ins.ins

23. Performance Reviews
hr_perf_review_api.create_perf_review
hr_perf_review_api.update_perf_review

24. State Information Taxes
hr_sit_api.update_sit
hr_sit_api.create_sit

25. Qualifications
per_qualifications_api.create_qualification
per_qualifications_api.update_qualification

26. Locations
hr_location_api.update_location
hr_location_api.create_location

27. Organization
hr_organization_api.update_organization
hr_organization_api.create_org_classification

28. If any Change in Organization information.
if information2 = 'Y' then
hr_organization_api.enable_org_classification
If any Change in Organization information.

29. if information2 = 'N' then
hr_organization_api.disable_org_classification

30. If Organization does not exist in instance
hr_organization_api.create_organization api

31. Jobs
hr_job_api.update_job
hr_job_api.create_job

32. Positions
hr_position_api.update_position
hr_position_api.create_position

The query to get the list of HRMS API's in Oracle is as follows:

select * from all_objects where object_name like 'HR%\_API' escape '\' and object_type = 'PACKAGE'
union
select * from all_objects where object_name like 'PAY%\_API' escape '\' and object_type = 'PACKAGE'
union
select * from all_objects where object_name like 'PER%\_API' escape '\' and object_type = 'PACKAGE'


Ur's
AmarAlam

HR Employee API to Create Employee in HRMS

DECLARE
   l_emp_num varchar2(20);
   l_person_id number;
   l_assignment_id number;
   l_asg_object_version_number number;
   l_effective_start_date date;
   l_effective_end_date date;
   l_full_name varchar2(240);
   l_per_comment_id number;
   l_assignment_sequence number;
   l_assignment_number per_all_assignments_f.assignment_number%TYPE;
   l_name_combination_warning boolean;
   l_assign_payroll_warning boolean;
   l_orig_hire_warning boolean;
   l_per_object_version_number number;
   l_asgobject_version_number number;
   l_comment_id number(5);
  CURSOR CUR_TP IS
 SELECT     tp.hire_date
    ,tp.business_group_id
    ,tp.last_name
      ,tp.sex
 FROM TEMP_PERSON tp ;
BEGIN
   for ap in CUR_TP loop
   l_emp_num:=null;
   begin
   HR_EMPLOYEE_API.CREATE_EMPLOYEE
   (
   p_hire_date                     =>    ap.hire_date
  ,p_business_group_id             =>    ap.business_group_id
  ,p_last_name                     =>    ap.last_name
  ,p_sex                           =>    ap.sex
  ,p_employee_number     =>  l_emp_num
  ,p_person_id       =>  l_person_id
  ,p_assignment_id       =>   l_assignment_id
  ,p_per_object_version_number     =>    l_per_object_version_number
  ,p_asg_object_version_number     =>    l_asgobject_version_number
  ,p_per_effective_start_date      =>    l_effective_start_date
  ,p_per_effective_end_date        =>    l_effective_end_date
  ,p_full_name                     =>    l_full_name
  ,p_per_comment_id            =>  l_comment_id
  ,p_assignment_sequence     =>  l_assignment_sequence
  ,p_assignment_number     =>  l_assignment_number
  ,p_name_combination_warning      =>  l_name_combination_warning
  ,p_assign_payroll_warning    =>  l_assign_payroll_warning
  ,p_orig_hire_warning     =>  l_orig_hire_warning
   );
end;
 END LOOP;
  COMMIT;
 END;
/


   hr_utility.set_message(801, 'HR_7208_API_BUS_GRP_INVALID');
   hr_utility.raise_error;


Ur's
AmarAlam