About Me

Dubai, United Arab Emirates
My name is Muhammad Faizan Karim and my aim is to spread the knowledge to everyone who want to learn oracle HRMS. I will try to write the video blogs for beginners so they can understand e-business suits from HRMS perspective, and if you have any difficulty in oracle applications HRMS , please feel free to contact with me at this email : faizan.kareem.ocp@gmail.com
Powered by Blogger.
Monday, December 29, 2014


XML Publisher Report Handling Special Characters 

Problem definition- The standard report runs fine when the output format is "Text" but errors out when it "XML".  The XML is generated partially with error message like "The XML page cannot be displayed or Authentication failed."

Solution-
Open the underlying RDF file and update the below value as
Once changed save the report , migrate it and test the XML output.


Thursday, November 20, 2014
Extract Fast Formula Text from ff_formulas_f table

Not sure if anyone came across this but I was struggling to extract all Payroll Fast Formulas and keep repository for config management. There is very simple way of extracting all formulas using TOAD (11.6.0.43).

    1)    Select formula_name ,formula_text from ff_formulas_f where formula_name like '%PRORA%'
    2)   This will retrun rows with formula name and formula text.
    3)   Right Click on Formula_Text field and select option Export Blobs.
    4)   Select Formula_Text in Export this column.
    5)   Select export path.
    6)   Select second option Export to Files name for the value in this column.
    7)   In this select Formula Name. You can choose file extension.
Hope this helps.
Wednesday, November 19, 2014
HRMS APIs

1.     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    := 'PRAJ_01';
 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('08-JUN-2011'),
               p_business_group_id                      => fnd_profile.value_specific('PER_BUSINESS_GROUP_ID'),
               p_last_name                                       => 'TEST',
               p_first_name                                       => 'PRAJKUMAR',
               p_middle_names                              => NULL,
               p_sex                                                     => 'M',
               p_national_identifier                       => '183-09-6723',
               p_date_of_birth                                 => TO_DATE('03-DEC-1988'),
               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 
        );
    COMMIT;
EXCEPTION
      WHEN OTHERS THEN
                    ROLLBACK;
                    dbms_output.put_line(SQLERRM);
END;

2.     Oracle HRMS API – Update Employee

API - hr_person_api.update_person
Example is for updating middle name and marital status
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               := 'PRAJ_01'; 
  
   -- 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('29-JUN-2011'),
          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('29-JUN-2011'),
            p_datetrack_update_mode         => lc_dt_ud_mode,
            p_person_id                                     => 32979,
            p_middle_names                            => 'TEST',
            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;

3.     Oracle HRMS API – Create Employee Contact

API - hr_contact_rel_api.create_contact
Example --
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('14-JUN-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('14-JUN-2011'),
           p_last_name                                     => 'TEST',
           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;

4.     Oracle HRMS API - Hire Into Job

API - hr_employee_api.hire_into_job
Example --
 
Consider a Contact for some employee already exist in Oracle System. Now that Contact has got Job, so his Person type should be converted to Employee from Contact (External)
 

 
Following API helps to create assignment for that Contact and helps to change his Person Type 
 
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('28-JUN-2012');
     lc_employee_number  VARCHAR2(100)  := 'CONTACT_TEST_01';
 
     -- 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('28-JUN-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;

5.     Oracle HRMS API – Create Employee Address

API - hr_person_address_api.create_person_address
Example --
 
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('08-JUN-2011'),
          p_person_id                           => 32979,
          p_primary_flag                     => 'Y',
          p_style                                     => 'US',
          p_date_from                           => TO_DATE('08-JUN-2011'),
          p_address_line1                   => '50 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;

6.     Oracle HRMS API – Update Employee Address

API - hr_person_address_api.update_person_address
 
Example --
Consider Employee having Address Line1 -- "50 Main Street"
Lets Update Address Line1 -- "60 Main Street" using update address API
DECLARE
      ln_address_id                         PER_ADDRESSES.ADDRESS_ID%TYPE;
      ln_object_version_number  PER_ADDRESSES.OBJECT_VERSION_NUMBER%TYPE := 1;

BEGIN
   -- Update Employee Address
   -- ----------------------------------------
    hr_person_address_api.update_person_address
    (    -- Input data elements
         -- -----------------------------
         p_effective_date                     => TO_DATE('10-JUN-2011'),
         p_address_id                          => 16406,
         p_address_line1                    => '60 Main Street',
         -- Output data elements
         -- --------------------------------
         p_object_version_number   => ln_object_version_number
    );
 
 COMMIT;


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

7.     Oracle HRMS API – Create Employee Element Entry

API - pay_element_entry_api.create_element_entry
Example --
Lets Try to Create Element Entry "Bonus" for Employee
DECLARE
   ln_element_link_id                  PAY_ELEMENT_LINKS_F.ELEMENT_LINK_ID%TYPE;
   ld_effective_start_date            DATE;
   ld_effective_end_date             DATE;
   ln_element_entry_id                PAY_ELEMENT_ENTRIES_F.ELEMENT_ENTRY_ID%TYPE;
   ln_object_version_number     PAY_ELEMENT_ENTRIES_F.OBJECT_VERSION_NUMBER %TYPE;
   lb_create_warning                    BOOLEAN;
   ln_input_value_id                    PAY_INPUT_VALUES_F.INPUT_VALUE_ID%TYPE;
   ln_screen_entry_value            PAY_ELEMENT_ENTRY_VALUES_F.SCREEN_ENTRY_VALUE%TYPE;
   ln_element_type_id                  PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID%TYPE;

BEGIN
        -- Get Element Link Id
        -- ------------------------------
          ln_element_link_id :=      hr_entry_api.get_link
                                                          (       p_assignment_id      => 33561,
                                                                  p_element_type_id   => 50417,
                                                                  p_session_date          => TO_DATE('23-JUN-2011')
                                                          );
 
      
dbms_output.put_line( '  API: Element Link Id: ' || ln_element_link_id );
 
       -- Create Element Entry
       -- ------------------------------
       
pay_element_entry_api.create_element_entry
         (     -- Input data elements
               -- -----------------------------
               p_effective_date                     => TO_DATE('22-JUN-2011'),
               p_business_group_id          => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
               p_assignment_id                   => 33561,
               p_element_link_id                => ln_element_link_id,
               p_entry_type                           => 'E',
               p_input_value_id1               => 53726,
               p_entry_value1                      => 2500,
               -- Output data elements
               -- --------------------------------
               p_effective_start_date          => ld_effective_start_date,
               p_effective_end_date           => ld_effective_end_date,
               p_element_entry_id             => ln_element_entry_id,
               p_object_version_number  => ln_object_version_number,
               p_create_warning                 => lb_create_warning
         );
 
    
dbms_output.put_line( '  API: pay_element_entry_api.create_element_entry successfull - Element Entry Id: ' || ln_element_entry_id );
 
 COMMIT;


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

8.     Oracle HRMS API – Delete Employee Element Entry

API --  pay_element_entry_api.delete_element_entry 
 
Example --
Consider Employee has Element Entry "Bonus". Lets try to Delete Element Entry "Bonus" using delete API
DECLARE
      ld_effective_start_date            DATE;
      ld_effective_end_date             DATE;
      lb_delete_warning                   BOOLEAN;
      ln_object_version_number    PAY_ELEMENT_ENTRIES_F.OBJECT_VERSION_NUMBER%TYPE := 1;

BEGIN
      -- Delete Element Entry
      -- -------------------------------
        pay_element_entry_api.delete_element_entry
        (    -- Input data elements
             -- ------------------------------
             p_datetrack_delete_mode    => 'DELETE',
             p_effective_date                      => TO_DATE('23-JUNE-2011'),
             p_element_entry_id               => 118557,
             -- Output data elements
             -- --------------------------------
             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_delete_warning                  => lb_delete_warning
        );
 
 COMMIT;


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


9.      Oracle HRMS API – Rehire Employee
API --  hr_employee_api.re_hire_ex_employee
 
Example --
Consider a Ex-Employee we will try to Rehire that employee using Rehire API
DECLARE
     ln_per_object_version_number      PER_ALL_PEOPLE_F.OBJECT_VERSION_NUMBER%TYPE        := 5;
     ln_assg_object_version_number    PER_ALL_ASSIGNMENTS_F.OBJECT_VERSION_NUMBER%TYPE;
     ln_assignment_id                               PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
     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;
     ln_assignment_sequence                  PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_SEQUENCE%TYPE;
     lb_assign_payroll_warning            BOOLEAN;
     lc_assignment_number                     PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_NUMBER%TYPE;


BEGIN
    -- Rehire Employee API 
    -- --------------------------------

     hr_employee_api.re_hire_ex_employee
     (    -- Input data elements
          -- -----------------------------
         p_hire_date                                          => TO_DATE('28-JUN-2011'),
         p_person_id                                         => 32979,
         p_rehire_reason                                  => NULL,
         -- Output data elements
         -- --------------------------------
        p_assignment_id                                => ln_assignment_id,
        p_per_object_version_number       => ln_per_object_version_number,
        p_asg_object_version_number       => ln_assg_object_version_number,
        p_per_effective_start_date               => ld_per_effective_start_date,
        p_per_effective_end_date                => ld_per_effective_end_date,
        p_assignment_sequence                  => ln_assignment_sequence,
        p_assignment_number                     => lc_assignment_number,
        p_assign_payroll_warning             => lb_assign_payroll_warning
    );
 
 COMMIT;


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

10. Oracle HRMS API – Create Employee Payment Method

API --  hr_personal_pay_method_api.create_personal_pay_method
 
Example --
DECLARE  
ln_method_id  PAY_PERSONAL_PAYMENT_METHODS_F.PERSONAL_PAYMENT_METHOD_ID%TYPE;
ln_ext_acc_id        PAY_EXTERNAL_ACCOUNTS.EXTERNAL_ACCOUNT_ID%TYPE;
ln_obj_ver_num    PAY_PERSONAL_PAYMENT_METHODS_F.OBJECT_VERSION_NUMBER%TYPE;
ld_eff_start_date   DATE;  
ld_eff_end_date    DATE;
ln_comment_id     NUMBER;

BEGIN
     -- Create Employee Payment Method
     -- --------------------------------------------------
      hr_personal_pay_method_api.create_personal_pay_method
      (   -- Input data elements
          -- ------------------------------
          p_effective_date                                     => TO_DATE('21-JUN-2011'),
          p_assignment_id                                   => 33561,
          p_org_payment_method_id               => 2,
          p_priority                                                 => 50,
          p_percentage                                           => 100,
          p_territory_code                                     => 'US',
          p_segment1                                              => 'PRAJKUMAR',
          p_segment2                                              => 'S',
          p_segment3                                              => '100200300',
          p_segment4                                              => '567',
          p_segment5                                              => 'HDFC',
          p_segment6                                              => 'INDIA',
          -- Output data elements
          -- --------------------------------
          p_personal_payment_method_id   => ln_method_id,
          p_external_account_id                       => ln_ext_acc_id,
          p_object_version_number                  => ln_obj_ver_num,
          p_effective_start_date                          => ld_eff_start_date,
          p_effective_end_date                           => ld_eff_end_date,
         p_comment_id                                        => ln_comment_id
     );
 
 COMMIT;
EXCEPTION
          WHEN OTHERS THEN
                          ROLLBACK;
                           dbms_output.put_line(SQLERRM);
END;

11. Oracle HRMS API – Create or Update Employee Phone

API --  hr_phone_api.create_or_update_phone
 
Example --
DECLARE
       ln_phone_id                              PER_PHONES.PHONE_ID%TYPE;
       ln_object_version_number    PER_PHONES.OBJECT_VERSION_NUMBER%TYPE;

BEGIN
   -- Create or Update Employee Phone Detail
   -- -----------------------------------------------------------
    hr_phone_api.create_or_update_phone
    (   -- Input data elements
        -- -----------------------------
        p_date_from                             => TO_DATE('13-JUN-2011'),
        p_phone_type                          => 'W1',
        p_phone_number                   => '9999999',
        p_parent_id                              => 32979,
        p_parent_table                         => 'PER_ALL_PEOPLE_F',
        p_effective_date                       => TO_DATE('13-JUN-2011'),
        -- Output data elements
        -- --------------------------------
        p_phone_id                              => ln_phone_id,
        p_object_version_number    => ln_object_version_number
     );
 
 COMMIT;
EXCEPTION
      WHEN OTHERS THEN
                    ROLLBACK;
                     dbms_output.put_line(SQLERRM);
END;

API --  hr_maintain_proposal_api.cre_or_upd_salary_proposal
 
Note - Salary Basis is required to be assigned to employee assignment before to run Salary Proposal API
Example
DECLARE
    lb_inv_next_sal_date_warning      BOOLEAN;
    lb_proposed_salary_warning         BOOLEAN;
    lb_approved_warning                       BOOLEAN;
    lb_payroll_warning                            BOOLEAN;
    ln_pay_proposal_id                           NUMBER;
    ln_object_version_number                NUMBER;

BEGIN
   -- Create or Upadte Employee Salary Proposal
   -- ----------------------------------------------------------------
    hr_maintain_proposal_api.cre_or_upd_salary_proposal
    (    -- Input data elements
         -- ------------------------------
         p_business_group_id                   => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
         p_assignment_id                            => 33561,
         p_change_date                                => TO_DATE('13-JUN-2011'),
         p_proposed_salary_n                   => 1000,
         p_approved                                      => 'Y',
         -- Output data elements
         -- --------------------------------
         p_pay_proposal_id                       => ln_pay_proposal_id,
         p_object_version_number           => ln_object_version_number,  
         p_inv_next_sal_date_warning  => lb_inv_next_sal_date_warning,
         p_proposed_salary_warning     => lb_proposed_salary_warning,
         p_approved_warning                   => lb_approved_warning,
         p_payroll_warning                        => lb_payroll_warning
    );
 
 COMMIT;


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

13. Oracle HRMS API –Update Employee Fed Tax Rule
API --  pay_federal_tax_rule_api.update_fed_tax_rule
Example --
DECLARE
   lb_correction                              BOOLEAN;
   lb_update                                   BOOLEAN;
   lb_update_override                 BOOLEAN;
   lb_update_change_insert      BOOLEAN;
   ld_effective_start_date            DATE;
   ld_effective_end_date             DATE;
   ln_assignment_id                     NUMBER                    := 33561;
   lc_dt_ud_mode                          VARCHAR2(100)     := NULL;
   ln_object_version_number     NUMBER                    := 0;
   ln_supp_tax_override_rate    PAY_US_EMP_FED_TAX_RULES_F.SUPP_TAX_OVERRIDE_RATE%TYPE;
   ln_emp_fed_tax_rule_id         PAY_US_EMP_FED_TAX_RULES_F.EMP_FED_TAX_RULE_ID%TYPE;


BEGIN
   -- Find Date Track Mode
   -- -------------------------------
   dt_api.find_dt_upd_modes
   (   -- Input data elements
       -- ------------------------------
      p_effective_date                   => TO_DATE('12-JUN-2011'),
      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 Fed Tax Rule
  -- ----------------------------------------------

  pay_federal_tax_rule_api.update_fed_tax_rule
  (   -- Input data elements
      -- -----------------------------
      p_effective_date                        => TO_DATE('20-JUN-2011'),
      p_datetrack_update_mode   => lc_dt_ud_mode,
      p_emp_fed_tax_rule_id         => 7417,
      p_withholding_allowances  => 100,
      p_fit_additional_tax                => 10,
      p_fit_exempt                               => 'N',
      p_supp_tax_override_rate     => 5,
      -- Output data elements
      -- --------------------------------

     p_object_version_number       => ln_object_version_number,
     p_effective_start_date               => ld_effective_start_date,
     p_effective_end_date                => ld_effective_end_date
  );
 
 COMMIT;

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

14. Oracle HRMS API – Create Employee State Tax Rule

API --  pay_state_tax_rule_api.create_state_tax_rule
Example --
DECLARE 
   
lc_dt_ud_mode                     VARCHAR2(100)     := NULL; 
    ln_assignment_id                 NUMBER                    := 33561;

    lb_correction                            BOOLEAN; 
    lb_update                                 BOOLEAN; 
    lb_update_override               BOOLEAN; 
    lb_update_change_insert    BOOLEAN;

    ln_emp_state_tax_rule_id   PAY_US_EMP_STATE_TAX_RULES_F.EMP_STATE_TAX_RULE_ID%TYPE;
    ln_object_version_number  NUMBER; 
    ld_effective_start_date          DATE; 
    ld_effective_end_date           DATE;


BEGIN
      -- Find Date Track Mode
      -- --------------------------------
        dt_api.find_dt_upd_modes
        (     p_effective_date                  => TO_DATE('12-JUN-2011'),
              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;
 
   -- Create Employee State Tax Rule
   -- -----------------------------------------------
    pay_state_tax_rule_api.create_state_tax_rule
    (    -- Input Parameters
         -- --------------------------
         p_effective_date                         => TO_DATE('15-JUN-2011'),
         p_default_flag                            => 'Y',
         p_assignment_id                      => 33561,
         p_state_code                               => '05',
         -- Output Parameters
         -- ----------------------------
        p_emp_state_tax_rule_id        => ln_emp_state_tax_rule_id,
        p_object_version_number       => ln_object_version_number,
        p_effective_start_date               => ld_effective_start_date,
        p_effective_end_date                => ld_effective_end_date
  );
 
 COMMIT;


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

15. Oracle HRMS API – Update Employee State Tax Rule

API --  pay_state_tax_rule_api.update_state_tax_rule
Example --
API --  pay_state_tax_rule_api.update_state_tax_rule
Example --
 
DECLARE
     lc_dt_ud_mode                       VARCHAR2(100)   := NULL;
     ln_assignment_id                  NUMBER                  := 33561;
     ln_object_version_number  NUMBER                  := 1;
     ld_effective_start_date          DATE;
     ld_effective_end_date            DATE;
     lb_correction                            BOOLEAN;
     lb_update                                  BOOLEAN;
     lb_update_override                BOOLEAN;
     lb_update_change_insert    BOOLEAN;

BEGIN
    -- Find Date Track Mode
    -- --------------------------------
     dt_api.find_dt_upd_modes
     (   p_effective_date                 => TO_DATE('12-JUN-2011'),
         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 State Tax Rule
   -- ---------------------------------
    pay_state_tax_rule_api.update_state_tax_rule
    (     -- Input data elements
          -- ------------------------------
          p_effective_date                        => TO_DATE('20-JUN-2011'),
          p_datetrack_update_mode   => lc_dt_ud_mode,
          p_emp_state_tax_rule_id      => 8455,
          p_withholding_allowances  => 100,
          p_sit_additional_tax               => 10,
          p_sit_exempt                              => 'N',
          -- Output data elements
          -- --------------------------------
          p_object_version_number      => ln_object_version_number,
          p_effective_start_date              => ld_effective_start_date,
          p_effective_end_date               => ld_effective_end_date
     );

 COMMIT;

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

16. Oracle HRMS API – Update Employee Assignment

To Update
Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id
API -- hr_assignment_api.update_emp_asg
 
To Update
Grade, Location, Job, Payroll, Organization, Employee Category, People Group
API -- hr_assignment_api.update_emp_asg_criteria
 
Example --
 
DECLARE
   -- Local Variables
   -- -----------------------
   lc_dt_ud_mode           VARCHAR2(100)    := NULL;
   ln_assignment_id       NUMBER                  := 33561;
   ln_supervisor_id        NUMBER                  := 2;
   ln_object_number       NUMBER                  := 1;
   ln_people_group_id  NUMBER                  := 1;
 
   -- 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 Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id       HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   ln_comment_id                             PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning        BOOLEAN;
 -- Out Variables for Update Employee Assgment Criteria
 -- -------------------------------------------------------------------------------
 ln_special_ceiling_step_id                    PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                                          VARCHAR2(30);
 ld_effective_start_date                             PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 ld_effective_end_date                              PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning   BOOLEAN;
 lb_other_manager_warning                  BOOLEAN;
 lb_spp_delete_warning                          BOOLEAN;
 lc_entries_changed_warning                VARCHAR2(30);
 lb_tax_district_changed_warn             BOOLEAN;
 
BEGIN
   -- Find Date Track Mode
   -- --------------------------------
   dt_api.find_dt_upd_modes
   (    p_effective_date                  => TO_DATE('12-JUN-2011'),
        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 Assignment
  -- ---------------------------------------------
 hr_assignment_api.update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => NULL,
  p_change_reason                           => NULL,
  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_segment1                                       => 204,
  p_segment3                                       => 'N',
  p_normal_hours                              => 10,
  p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );
 
 -- Find Date Track Mode for Second API 
 -- ------------------------------------------------------
  dt_api.find_dt_upd_modes
  (  p_effective_date                   => TO_DATE('12-JUN-2011'),
     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 Assgment Criteria
 -- -----------------------------------------------------
 hr_assignment_api.update_emp_asg_criteria
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                                   => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode               => lc_dt_ud_mode,
  p_assignment_id                                 => ln_assignment_id,
  p_location_id                                        => 204,
  p_grade_id                                             => 29,
  p_job_id                                                  => 16,
  p_payroll_id                                          => 52,
  p_organization_id                               => 239,
  p_employment_category                    => 'FR',
  -- Output data elements
  -- -------------------------------
  p_people_group_id                              => ln_people_group_id,
  p_object_version_number                   => ln_object_number,
  p_special_ceiling_step_id                  => ln_special_ceiling_step_id,
  p_group_name                                        => lc_group_name,
  p_effective_start_date                           => ld_effective_start_date,
  p_effective_end_date                             => ld_effective_end_date,
  p_org_now_no_manager_warning  => lb_org_now_no_manager_warning,
  p_other_manager_warning                 => lb_other_manager_warning,
  p_spp_delete_warning                         => lb_spp_delete_warning,
  p_entries_changed_warning              => lc_entries_changed_warning,
  p_tax_district_changed_warning     => lb_tax_district_changed_warn
 );
 
 COMMIT;
EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;