Oracle EBS (ERP): HRMS

Showing posts with label HRMS. Show all posts

Oracle R12 Assign Supervisor to Employees

DECLARE

l_effective_date DATE := SYSDATE;

l_assignment_id NUMBER;

l_obj NUMBER;

l_assignment_number VARCHAR2 (40);

l_supervisor_id NUMBER;

lo_soft_coding_keyflex_id NUMBER;

o_concatenated_segments VARCHAR2 (240);

o_comment_id NUMBER;

o_effective_start_date DATE;

o_effective_end_date DATE;

o_no_managers_warning BOOLEAN;

o_other_manager_warning BOOLEAN;

CURSOR CUR

SELECT distinct paaf.assignment_number l_assignment_number, paaf.object_version_number l_obj,assignment_id l_assignment_id, --, papf1.person_id,

(SELECT person_id FROM per_all_people_f WHERE employee_number = ges.supervisor_num

AND TRUNC(SYSDATE) BETWEEN TRUNC(effective_start_date) AND TRUNC(effective_end_date)) l_supervisor_id

FROM per_all_assignments_f paaf,

per_assignment_status_types past,

per_all_people_f papf1,

jg_emp_stg_tbl ges

WHERE 1=1

AND papf1.employee_number = ges.employee_number

AND ges.employee_number <> '555'

AND TRUNC(SYSDATE) BETWEEN TRUNC(paaf.effective_start_date)

AND TRUNC(paaf.effective_end_date)

AND UPPER(past.user_status) = 'ACTIVE ASSIGNMENT'

AND papf1.person_id = paaf.person_id

AND ges.CREATION_DATE is not null

AND past.assignment_status_type_id = paaf.assignment_status_type_id;

BEGIN

FOR C1 IN CUR

LOOP

DBMS_OUTPUT.PUT_LINE(C1.l_assignment_number || 'Employee Supervisor is :'||C1.l_supervisor_id);

hr_assignment_api.update_emp_asg

(p_effective_date => l_effective_date

,p_datetrack_update_mode => 'CORRECTION'

,p_assignment_id => C1.i_assignment_id --i_assignment_id

,p_assignment_number => C1.l_assignment_number

,p_object_version_number => C1.l_obj

,p_supervisor_id => C1.l_supervisor_id

-- OUT Variables

,p_soft_coding_keyflex_id => lo_soft_coding_keyflex_id

,p_concatenated_segments => o_concatenated_segments

,p_comment_id => o_comment_id

,p_effective_start_date => o_effective_start_date

,p_effective_end_date => o_effective_end_date

,p_no_managers_warning => o_no_managers_warning

,p_other_manager_warning => o_other_manager_warning

);

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Employee Supervisor is updated Successful !!');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error updating Employee Supervisor : '||SQLERRM);

END;

Oracle R12 employee update script hr_assignment_api.update_emp_asg in HRMS

--Employee Expense Account update and Assignment

CREATE TABLE xx_emp_code_comb_tbl

(

emp_code VARCHAR2(30 BYTE),

emp_full_name VARCHAR2(240 BYTE),

creation_date DATE,

ledger VARCHAR2(240 BYTE),

code_combination VARCHAR2(240 BYTE),

status VARCHAR2(1 BYTE),

code_comb_id NUMBER

)

---Below is the script for update the assignment

DECLARE

i_effective_date DATE := SYSDATE;

i_assignment_id NUMBER ;--:= 407; -- Pass assignment_id created by hr_employee_api.create_employee

i_assignment_number VARCHAR2(30) :='JG123'; -- Pass parameter assignment_number created by hr_employee_api.create_employee

l_obj NUMBER;

l_assignment_number VARCHAR2(30);

l_supervisor_id NUMBER;

lo_soft_coding_keyflex_id NUMBER;

o_concatenated_segments VARCHAR2(240);

o_comment_id NUMBER;

o_effective_start_date DATE;

o_effective_end_date DATE;

o_no_managers_warning BOOLEAN;

o_other_manager_warning BOOLEAN;

Cursor C1

SELECT paaf.assignment_number,paaf.object_version_number, assignment_id,a.code_comb_id,paaf.effective_start_date

FROM per_all_assignments_f paaf,

xx_emp_code_comb_tbl a,

per_assignment_status_types past

WHERE 1=1 --assignment_id = i_assignment_id

AND paaf.assignment_number = a.emp_code

AND TRUNC(SYSDATE) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)

AND UPPER(past.user_status) = 'ACTIVE ASSIGNMENT'

AND past.assignment_status_type_id = paaf.assignment_status_type_id;

BEGIN

FOR i IN C1 LOOP

hr_assignment_api.update_emp_asg

(p_effective_date => i.effective_start_date

,p_datetrack_update_mode => 'CORRECTION'

,p_assignment_id => i.assignment_id

,p_assignment_number => i.assignment_number

,p_object_version_number => i.object_version_number

--,p_supervisor_id => 65 --l_supervisor_id

,p_default_code_comb_id => i.code_comb_id

,p_set_of_books_id => 2041

-- OUT Variables

,p_soft_coding_keyflex_id => lo_soft_coding_keyflex_id

,p_concatenated_segments => o_concatenated_segments

,p_comment_id => o_comment_id

,p_effective_start_date => o_effective_start_date

,p_effective_end_date => o_effective_end_date

,p_no_managers_warning => o_no_managers_warning

,p_other_manager_warning => o_other_manager_warning

);

END LOOP;

COMMIT;

DBMS_OUTPUT.PUT_LINE('Employee Supervisor is updated');

EXCEPTION

WHEN OTHERS

THEN

DBMS_OUTPUT.PUT_LINE('Error while updating Employee Supervisor : '||SQLERRM);

END;

HRMS: SIT Assignment to BGs Script

declare

l_sp_info_type_id number;
l_flex_num number;
cursor c1 is
select business_Group_id
from per_special_info_types
where id_flex_num=<Sample SIT where required bg available>
order by 1;
begin
select id_flex_num
into l_flex_num
from fnd_id_flex_Structures
where id_flex_structure_code='<XXXXXXXXXX>';

for NEW_ASSN in C1
loop
per_sit_pkg.ins_sit ( p_special_information_type_id => l_sp_info_type_id,
p_id_flex_num => l_flex_num,
p_comments => null,
p_enabled_flag => null,
p_request_id => null,
p_program_application_idd => null,
p_program_id => null,
p_program_update_date => null,
p_attribute_category => null,
p_attribute1 => null,
p_attribute2 => null,
p_attribute3 => null,
p_attribute4 => null,
p_attribute5 => null,
p_attribute6 => null,
p_attribute7 => null,
p_attribute8 => null,
p_attribute9 => null,
p_attribute10 => null,
p_attribute11 => null,
p_attribute12 => null,
p_attribute13 => null,
p_attribute14 => null,
p_attribute15 => null,
p_attribute16 => null,
p_attribute17 => null,
p_attribute18 => null,
p_attribute19 => null,
p_attribute20 => null,
p_multiple_occurrences_flag => null,
);
per_sit_pkg.reset_usages( p_special_information_type_id => l_sp_info_type_id,
p_job_category => null,
p_position_category => null,
p_skill_category => null,
p_other_category => null,
p_osha_category => null,
p_ada_category => null
);

end loop;

end;

How to find Full Infomation Employee in Oracle R12

SELECT   person_id,
         title,
         first_name,
         middle_names,
         last_name,
         Decode(sex,'M', 'Male','F','Female','NA') Gender,
         date_of_birth,
         Decode(marital_status,'M', 'Married','S', 'Single') Marital_Status,
         nationality,
         national_identifier,
         resume_exists,
         email_address,
         town_of_birth,
         business_group_id,
         employee_number,
         full_name,
         original_date_of_hire,
         party_id
FROM   per_all_people_f prf
WHERE   1 = 1
         AND TRUNC (SYSDATE) BETWEEN prf.effective_start_date
                                 AND prf.effective_end_date;