Set Employee Number sequence In Oracle Apps

To change the sequence number  of the Employee number in Oracle Apps following steps needs to be followed:

 

Run the following SQL statements

SQL> SELECT  HR.PER_GLOBAL_EMP_NUM_S.NEXTVAL FROM DUAL;

 

Depending on the results, alter the sequence so that the NEXTVAL number for the first employee during data migration is 70001

 

If the results is:

NEXTVAL

———-

1000

 

Then run the alter statement:

 

SQL> ALTER SEQUENCE HR.PER_GLOBAL_EMP_NUM_S increment by 69999

 

SQL> SELECT  HR.PER_GLOBAL_EMP_NUM_S.NEXTVAL

FROM DUAL;

 

NEXTVAL

———-

70000

 

To ensure that the next employee number is 70001, run the alter statement:

 

SQL> ALTER SEQUENCE HR.PER_GLOBAL_EMP_NUM_S increment by 1

 

The next value generated would be 70001

 

For employee number parameter in API, use HR.PER_GLOBAL_EMP_NUM_S.NEXTVAL defined in create employee API

 

hr_employee.create_employee

(…

,p_employee_number => HR.PER_GLOBAL_EMP_NUM_S.NEXTVAL

Advertisements

Terminating an Employee

To terminate an employee:

        1. Optionally, enter the reason for the termination.

 

        2. Enter the termination dates. Only the Actual date is required. This is the date when the employee’s person type changes to Ex-employee. The Notified and Projected dates are for information only. The Final Process date is a date after which no further pay processing for the employee can occur, if you are using Oracle Payroll.
      For the Final Process date:
      • If you may need to process pay for the employee after termination, set the Final Process date later than the Actual date, or leave it blank

    This means that the employee’s assignments are given the default user status for the HR System Status Terminate Assignment

        . If there is more than one user status defined for this system status, when you choose the Terminate button the system prompts you to select which status to enter on the employee’s assignments.
      • If you do not need to continue processing, set the Final Process date to the Actual date.

    Note: If you are an Oracle Payroll user, you must also enter a Last Standard Process Date. This is the last date for normal processing, while the Final Process date is the last date for late payments. Element entries are closed down on the Last Standard Process, the Actual date, or Final Process date, depending on how you have defined the elements.

        3. When the information is complete, choose the Terminate button to complete the termination.

    Create SIT API

    procedure xxhr_bank_details
    /* =====================================================================
    — NAME : xxhr_bank_details

    — PROGRAM TYPE : Procedure

    — DESCRIPTION : This procedure is used to update the bank details
    — INPUTS : None
    — OUTPUTS : None
    — ===================================================================*/
    is

    /*
    ================================================================================
    –Cursor to get data from Staging Table
    ================================================================================
    */
    CURSOR c_bank
    IS
    SELECT
    RECORD_NO,
    EMPLOYEE_NUMBER ,
    BANK_CODE ,
    BANK_ACC_NUMBER ,
    START_DATE ,
    IBAN ,
    SWIFT_CODE ,
    PAYMENT_CURRENCY
    FROM xxhr_emp_bank_stg
    WHERE process_flag IS NULL;

    sql_err VARCHAR2(3000);
    l_num_id_flex_num NUMBER;
    l_num_analysis_criteria_id NUMBER;
    l_num_person_analysis_id NUMBER;
    l_num_pea_ovn NUMBER;
    l_num_person_id NUMBER;
    l_num_SUCCESSFUL_REC NUMBER;
    l_num_record_no NUMBER;
    l_var_flag VARCHAR2(10);
    end_time DATE;
    l_num_failed_rec NUMBER;
    l_num_discarded_records NUMBER;
    start_time DATE;
    l_var_bank_code VARCHAR2(100);
    l_var_payment_currency VARCHAR2(100);
    BANK_CODE_NULL EXCEPTION;
    BANK_ACC_NUMBER_NULL EXCEPTION;

    begin
    start_time := sysdate;

    Begin
    SELECT
    ID_FLEX_NUM
    into l_num_id_flex_num
    FROM FND_ID_FLEX_SEGMENTS_VL
    WHERE 1=1
    AND (ID_FLEX_CODE =’PEA’)
    AND (APPLICATION_ID=800)
    and upper(SEGMENT_NAME)like ‘%BANK%’;
    Exception when others then
    sql_err:= sqlerrm;
    DBMS_OUTPUT.put_line (‘exception’||sql_err);

    end;

    FOR r_bank IN c_bank
    LOOP

    l_num_record_no := r_bank.record_no;

    /*
    ================================================================================
    –getting person id
    ================================================================================
    */
    begin
    select person_id
    into l_num_person_id
    from per_all_people_f
    where
    employee_number = r_bank.employee_number
    and sysdate between effective_start_date and effective_end_date
    and business_group_id = fnd_profile.value(‘PER_BUSINESS_GROUP_ID’);– 82;
    exception when others then
    l_var_flag := ‘N’;
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = ’employee does not exists’
    WHERE record_no = l_num_record_no;
    DBMS_OUTPUT.put_line (’employee does not exists’);
    fnd_file.put_line(fnd_file.LOG,’employee does not exists for emp no ‘||r_bank.employee_number);
    end;

    /*
    ================================================================================
    –Validation for Bank Code
    ================================================================================
    */
    BEGIN

    IF r_bank.BANK_CODE IS NULL THEN
    RAISE BANK_CODE_NULL;
    END IF;
    EXCEPTION
    when BANK_CODE_NULL then
    l_var_flag := ‘N’;
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = ‘Bank Code is null’
    WHERE record_no = l_num_record_no;
    DBMS_OUTPUT.put_line (‘Bank Code cannot be null’);
    fnd_file.put_line(fnd_file.LOG,’Bank Code is null for for emp no ‘||r_bank.employee_number);
    END;

    /*
    ================================================================================
    –Validation for BANK_ACC_NUMBER
    ================================================================================
    */
    BEGIN

    IF r_bank.BANK_ACC_NUMBER IS NULL THEN
    RAISE BANK_ACC_NUMBER_NULL;
    END IF;
    EXCEPTION
    when BANK_ACC_NUMBER_NULL then
    l_var_flag := ‘N’;
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = ‘Bank A/c number is null’
    WHERE record_no = l_num_record_no;
    DBMS_OUTPUT.put_line (‘Bank A/c number cannot be null’);
    fnd_file.put_line(fnd_file.LOG,’Bank A/c number is null for for emp no ‘||r_bank.employee_number);
    END;

    /*
    ================================================================================
    –Validation for BANK CODE
    ================================================================================
    */

    IF r_bank.BANK_CODE IS NOT NULL THEN
    BEGIN
    select ffv.flex_value
    into l_var_bank_code
    from
    fnd_flex_value_sets ffvs,
    fnd_flex_values ffv
    where ffvs.flex_value_set_name =’TEC_KL_BANK_CODE’
    and ffv.flex_value_set_id = ffvs.flex_value_set_id;
    EXCEPTION
    when others then
    l_var_flag := ‘N’;
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = ‘Bank code incorrect’
    WHERE record_no = l_num_record_no;
    DBMS_OUTPUT.put_line (‘Bank code incorrect’);
    fnd_file.put_line(fnd_file.LOG,’Bank code incorrect for for emp no ‘||r_bank.employee_number);

    END;
    END IF;

    /*
    ================================================================================
    –Validation for Payment Currency
    ================================================================================
    */

    IF r_bank.PAYMENT_CURRENCY IS NOT NULL THEN
    BEGIN
    select ffv.flex_value
    into l_var_payment_currency
    from
    fnd_flex_value_sets ffvs,
    fnd_flex_values ffv
    where ffvs.flex_value_set_name =’TEC_KL_PAY_CURR’
    and ffv.flex_value_set_id = ffvs.flex_value_set_id;
    EXCEPTION
    when others then
    l_var_flag := ‘N’;
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = ‘Bank code incorrect’
    WHERE record_no = l_num_record_no;
    DBMS_OUTPUT.put_line (‘Bank code incorrect’);
    fnd_file.put_line(fnd_file.LOG,’Payment Currency incorrect for for emp no ‘||r_bank.employee_number);

    END;
    END IF;

    /*
    ================================================================================
    –Update Local Employee Number
    ================================================================================
    */

    BEGIN
    — DBMS_OUTPUT.put_line (‘XXSSSSSSS’);
    l_num_person_analysis_id :=0;
    hr_sit_api.create_sit
    (p_validate => FALSE,
    p_person_id => l_num_person_id,
    p_business_group_id => fnd_profile.value(‘PER_BUSINESS_GROUP_ID’),–82,
    p_id_flex_num => l_num_id_flex_num,
    p_effective_date => SYSDATE,
    p_comments => NULL,
    p_date_from => SYSDATE,
    — p_date_to => NULL,
    p_request_id => NULL,
    p_program_application_id => NULL,
    p_program_id => NULL,
    p_program_update_date => NULL,
    p_attribute_category => NULL,
    p_segment1 => l_var_bank_code,
    p_segment2 => r_bank.BANK_ACC_NUMBER,
    p_segment3 => r_bank.iban,
    p_segment4 => r_bank.swift_code,
    p_segment5 => l_var_payment_currency,
    p_analysis_criteria_id => l_num_analysis_criteria_id,
    p_person_analysis_id => l_num_person_analysis_id,
    p_pea_object_version_number => l_num_pea_ovn
    );

    l_num_SUCCESSFUL_REC := l_num_SUCCESSFUL_REC + 1;
    /*
    ================================================================================
    –Update XXHR_EMP_MASTER_STG staging table
    ================================================================================
    */
    UPDATE xxhr_emp_bank_stg
    SET PROCESS_FLAG = ‘S’
    WHERE record_no = l_num_record_no;
    commit;

    exception when others then
    l_var_flag := ‘N’;
    fnd_file.put_line (fnd_file.LOG,
    ‘unable to insert bank details for employee Number ‘||r_bank.employee_number
    );
    UPDATE xxhr_emp_bank_stg
    SET
    PROCESS_FLAG = ‘E’,
    ERROR_MSG =’unable to insert bank detailsn for employee Number ‘||r_bank.employee_number
    WHERE record_no = l_num_record_no;
    commit;

    end;
    IF l_var_flag = ‘N’
    THEN
    l_num_FAILED_REC := l_num_FAILED_REC + 1;
    END IF;

    end loop;
    end_time:=sysdate;
    DBMS_OUTPUT.put_line ( ‘ Number of successful records is ‘
    || l_num_successful_rec
    || ‘ , failed records is ‘
    || l_num_failed_rec
    || ‘ and discarded records is ‘
    || l_num_discarded_records||’ Time taken was ‘||to_char(24*60*(end_time-start_time))
    );
    fnd_file.put_line (fnd_file.LOG,
    ‘Number of successful records is ‘
    || l_num_successful_rec
    || ‘ , failed records is ‘
    || l_num_failed_rec
    || ‘ and discarded records is ‘
    || l_num_discarded_records
    ||’, Time taken was ‘||to_char(24*60*(end_time-start_time))||’ mins’
    );

    commit;

    end xxhr_bank_details;

    Delete SIT API

    API for Deleting SIT details.

     

     

     

    PROCEDURE XXHR_DELETE_SIT
    /* =====================================================================
    — NAME : XXHR_DELETE_SIT

    — PROGRAM TYPE : Procedure

    — DESCRIPTION : This is used to delete Special Information type for any employee
    — INPUTS : None
    — OUTPUTS : None
    — ===================================================================*/
    IS

    cursor cur_del_sit
    is
    select *
    FROM XXHR_EMP_MASTER_STG
    where process_flag is null;
    ln_person_id number;
    ln_person_analysis_id number;
    ln_ovn number;
    lv_err varchar2(3000);
    lv_status varchar2(100);

    begin
    for cur_del_sit_rec in cur_del_sit
    loop
    begin
    select person_id into ln_person_id
    from per_all_people_f papf
    where employee_number = cur_del_sit_rec.employee_number
    and sysdate between effective_start_date and effective_end_date;
    exception when others then
    ln_person_id := null;
    end;
    begin
    select distinct ppa. person_analysis_id ,ppa.object_version_number
    into ln_person_analysis_id, ln_ovn
    from PER_ANALYSIS_CRITERIA pac,
    FND_ID_FLEX_SEGMENTS_VL fifs,
    PER_PERSON_ANALYSES ppa
    where 1=1
    –pac.ID_FLEX_NUM =50448
    and pac.id_flex_num = fifs.id_flex_num
    and fifs.segment_name like ‘Passport Number’
    and ppa.person_id = ln_person_id
    and ppa.id_flex_num = pac.id_flex_num;
    exception when others then
    lv_status := ‘N’;
    null;
    end;
    if nvl(lv_status,’Y’) =’Y’
    then
    begin

    hr_sit_api.delete_sit
    (p_validate =>FALSE
    ,p_person_analysis_id => ln_person_analysis_id
    ,p_pea_object_version_number => ln_ovn
    );

    UPDATE XXHR_EMP_MASTER_STG
    SET PROCESS_FLAG = ‘S’
    — ERROR_MSG = ‘last name is null’
    WHERE employee_number = cur_del_sit_rec.employee_number;

    exception when others then
    lv_err := sqlerrm;
    UPDATE XXHR_EMP_MASTER_STG
    SET PROCESS_FLAG = ‘E’,
    ERROR_MSG = lv_err
    WHERE employee_number = cur_del_sit_rec.employee_number;
    end;

    end if;
    end loop;
    end XXHR_DELETE_SIT;

    API to Re Hire Contingent Worker

    API to re hire Ex contingent Worker

     

     

    Procedure XXHR_CWK_REHIRE
    as
    /* =====================================================================
    — NAME : XXHR_CWK_REHIRE

    — PROGRAM TYPE : Procedure

    — DESCRIPTION : This is used to rehire an CWK
    — INPUTS : None
    — OUTPUTS : None
    — ===================================================================*/
    l_dat_actual_term_date DATE;
    l_bool_warn BOOLEAN;
    lv_err varchar2(3000);
    l_num_person_id NUMBER;

    cursor c_cwk_rehire
    IS
    select *
    from
    XXHR_EUREH_GEOS_PERSONAL
    where
    process_flag = ‘EX-CWK REHIRE’;
    BEGIN
    for r_cwk_rehire in c_cwk_rehire
    loop

     

    begin
    select person_id
    into l_num_person_id
    from per_all_people_f
    where npw_number = r_cwk_rehire.euhreka_employee_number
    and sysdate between effective_start_date and effective_end_date
    and business_group_id = fnd_profile.value(‘PER_BUSINESS_GROUP_ID’);
    exception when others then
    update xxhr_eureh_geos_personal
    set error_msg =’CWK person_id not found’,
    process_flag = ‘E’
    where process_id = r_cwk_rehire.process_id;
    dbms_output.put_line(‘person id not found’);
    fnd_file.put_line(fnd_file.LOG,’CWK person_id not found’||r_cwk_rehire.process_id);
    end;

     

    BEGIN
    SELECT pp.actual_termination_date
    INTO l_dat_actual_term_date
    FROM PER_PERIODS_OF_PLACEMENT_V pp,
    per_all_people_f pf
    WHERE pp.PERSON_ID = pf.person_id
    AND pf.business_group_id = FND_PROFILE.value(‘PER_BUSINESS_GROUP_ID’)
    and pf.person_id = l_num_person_id
    and sysdate between pf.effective_start_date and pf.effective_end_date;

    exception when others then
    lv_err:= sqlerrm;
    update xxhr_eureh_geos_personal
    set error_msg =’termination date not found’,
    process_flag = ‘E’
    where process_id = r_cwk_rehire.process_id;
    fnd_file.put_line(fnd_file.LOG,’CWK termionation date not found’||r_cwk_rehire.process_id || lv_err);
    END;

    dbms_output.put_line(‘actual term date ‘||l_dat_actual_term_date);
    BEGIN
    Hr_Contingent_Worker_Api.reverse_terminate_placement
    (
    p_validate => false,
    p_person_id =>l_num_person_id,
    p_actual_termination_date => l_dat_actual_term_date,
    p_clear_details =>’Y’,
    p_fut_actns_exist_warning => l_bool_warn

    );
    UPDATE xxhr_eureh_geos_personal
    set process_flag = ‘S’
    WHERE Process_id = r_cwk_rehire.process_id;

    exception when others then
    lv_err := sqlerrm;
    UPDATE xxhr_eureh_geos_personal
    set process_flag = ‘E’,
    error_msg = error_msg ||’–‘||’Unable to terminatte CWK’ ||lv_err
    WHERE Process_id = r_cwk_rehire.process_id;

    fnd_file.put_line(fnd_file.LOG,’Unable to terminatte CWK for record_number’||r_cwk_rehire.process_id);

    END;
    end loop;
    commit;
    end XXHR_CWK_REHIRE;

    API to Rehire of Ex- Employees

    Procedure for Rehire of Ex- Employees:

     

     

    Procedure XXHR_EMP_REHIRE
    as
    /* =====================================================================
    — NAME : XXHR_EMP_REHIRE

    — PROGRAM TYPE : Procedure

    — DESCRIPTION : This is used to rehire an employee
    — INPUTS : None
    — OUTPUTS : None
    — ===================================================================*/

    l_num_assg_ovn number;
    — ln_assignment_id NUMBER;
    l_dat_per_effective_start_date date;
    l_dat_per_effective_end_date date;
    l_num_assignment_sequence NUMBER;
    l_bool_assign_payroll_warning boolean;
    l_var_assignment_number VARCHAR2(100);
    l_num_per_ovn number;
    l_num_assignment_id number;
    lv_err varchar2(3000);
    l_num_person_id number;
    l_num_object_version_number number;
    l_num_discarded_records number:=0;
    l_num_successful_rec number:=0;
    l_num_failed_rec number:=0;
    l_var_flag varchar2(100);

    CURSOR c_emp
    IS
    SELECT LAST_NAME,
    first_name,
    birth_date,
    euhreka_employee_number,
    from_date,
    process_id
    from xxhr_eureh_geos_personal
    where
    process_flag =’EX-EMP REHIRE’;

    begin
    for r_emp in c_emp
    loop
    begin
    select person_id , object_version_number
    into l_num_person_id,
    l_num_object_version_number
    FROM per_all_people_f
    WHERE first_name = r_emp.first_name
    AND last_name = r_emp.last_name
    AND date_of_birth = r_emp.birth_date
    AND sysdate BETWEEN effective_start_date AND effective_end_date;
    EXCEPTION
    when others then
    l_var_flag := ‘N’;
    l_num_discarded_records := l_num_discarded_records +1 ;
    update xxhr_eureh_geos_personal
    set process_flag =’E’,
    error_msg =’Employee Not found for Rehire’
    where process_id = r_emp.process_id;
    fnd_file.put_line(fnd_file.LOG,’Emp not found for Rehire :’||r_emp.EUHREKA_EMPLOYEE_NUMBER );
    END;

    l_num_per_ovn := l_num_object_version_number;
    if nvl(l_var_flag, ‘Y’) = ‘Y’
    then
    begin
    hr_employee_api.re_hire_ex_employee
    ( — Input data elements
    — —————————–
    p_validate => false,
    p_hire_date => r_emp.from_date,
    p_person_id => l_num_person_id,
    p_rehire_reason => ‘ ‘,
    — Output data elements
    — ——————————–
    p_assignment_id => l_num_assignment_id,
    p_per_object_version_number => l_num_per_ovn,
    p_asg_object_version_number => l_num_assg_ovn,
    p_per_effective_start_date => l_dat_per_effective_start_date,
    p_per_effective_end_date => l_dat_per_effective_end_date,
    p_assignment_sequence => l_num_assignment_sequence,
    p_assignment_number => l_var_assignment_number,
    p_assign_payroll_warning => l_bool_assign_payroll_warning
    );
    l_num_SUCCESSFUL_REC := l_num_SUCCESSFUL_REC + 1;

    UPDATE XXHR_EUREH_GEOS_PERSONAL
    SET PROCESS_FLAG = ‘S’
    where process_id = r_emp.process_id;

    exception when others then
    lv_err := sqlerrm;
    l_num_failed_rec := l_num_failed_rec +1;
    UPDATE XXHR_EUREH_GEOS_PERSONAL
    SET PROCESS_FLAG = ‘E’,
    error_msg = lv_err
    where process_id = r_emp.process_id;
    fnd_file.put_line(fnd_file.LOG,’EMp Re Hire not done for :’||r_emp.EUHREKA_EMPLOYEE_NUMBER );
    end;

    end if;
    end loop;
    DBMS_OUTPUT.put_line ( ‘ Number of successful records for Emp Rehire is’
    || l_num_successful_rec
    || ‘ , failed records is ‘
    || l_num_failed_rec
    || ‘ and discarded records is ‘
    || l_num_discarded_records
    );
    fnd_file.put_line (fnd_file.log,
    ‘Number of successful records for Emp Rehire is ‘
    || l_num_successful_rec
    || ‘ , failed records is ‘
    || l_num_failed_rec
    || ‘ and discarded records is ‘
    || l_num_discarded_records
    );

    END XXHR_EMP_REHIRE;

     

    Organizational Hierarchy Query

    Query for getting Organizational Hierarchy
    SELECT
    LPAD(‘ ‘,10*(LEVEL-1)) || org.name hierarchy, org.organization_id
    FROM
    hr_all_organization_units org,
    per_org_structure_elements pose
    where 1=1
    AND org.organization_id = pose.organization_id_child
    AND pose.org_structure_version_id = 61
    –and org.name like ‘201.Financiale Services’
    start with
    pose.organization_id_parent = 89 — Orgnization of parent id — provide the id from which level the downward hierarchy should be displaed
    CONNECT BY PRIOR
    pose.organization_id_child = pose.organization_id_parent
    ORDER SIBLINGS BY
    org.location_id,
    pose.organization_id_child;