March 7, 2010

Data Pump Example Code

Data Pump Used for data upload in HRMS

It has Meta Mapper,Interface Tables,PL/SQL Routines,Data Pump Engine

Meta Mapper Creates the procedure Needed To map to HR API Parameters.Check the example
1) HR_PUMP_UTILS.CREATE_BATCH_HEADER
2) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
3) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES

***Using API***
1) HR_EMPLOYEE_API.CREATE_EMPLOYEE
2) HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA
3) HR_PUMP_UTILS.CREATE_BATCH_HEADER
4) HRDPP_CREATE_EMPLOYEE.INSERT_BATCH_LINES
5) HRDPP_UPDATE_EMP_ASG_CRITERIA.INSERT_BATCH_LINES


Declare
L_assignment_number varchar2 (20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);/* while update needed for exisiting assigment,use employee id and name as identifier(unique combination) as key*/
l_asg_user_key varchar2(100);
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','MY Company');
l_pers_user_key := 'Wise' '25-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Wise' feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'WISE'
,p_sex => 'M'
,p_date_of_birth => '25-feb-1978');
commit;
end;

declare
l_organization_name varchar2(20);
l_language_code varchar2(20);
l_batch_id number;
l_asg_user_key varchar2(100);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('Assign Batch Header','My Company');
l_asg_user_key := 'Wise' '25-feb-1978' ' : ASG USER KEY';
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines
(p_batch_id => l_batch_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;
end;

declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;
begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Aise' '26-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Aise' '26-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Aise'
,p_sex => 'M'
,p_date_of_birth => '26-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

Commit;
End;
Declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2 (50);
L_PEOPLE_GROUP_ID NUMBER;

begin
l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';
hrdpp_create_employee.insert_batch_lines(p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key,
p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');
L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;
hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);
commit;
end;
declare
l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;

begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Bise' '20-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Bise' '20-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(
p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Bise'
,p_sex => 'M'
,p_date_of_birth => '20-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(
p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_JOB_NAME => 'HR009.HR CONSULTANT'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;


declare

l_assignment_number varchar2(20);
l_emp_no varchar2(20);
l_batch_id number;
l_pers_user_key varchar2(100);
l_asg_user_key varchar2(100);
l_organization_name varchar2(20);
l_language_code varchar2(20);
L_SPECIAL_CEILING_ID NUMBER;
L_P_CON_SEG_USER_NAME VARCHAR2(50);
L_PEOPLE_GROUP_ID NUMBER;
begin

l_batch_id := hr_pump_utils.create_batch_header('S1 Batch Header','My Company');
l_pers_user_key := 'Sise' '2-feb-1978' ' : PER USER KEY';
l_asg_user_key := 'Sise' '2-feb-1978' ' : ASG USER KEY';

hrdpp_create_employee.insert_batch_lines(

p_batch_id => l_batch_id
,p_person_user_key => l_pers_user_key
,p_assignment_user_key => l_asg_user_key
,p_hire_date => trunc(sysdate)
,p_employee_number => l_emp_no
,p_last_name => 'Sise'
,p_sex => 'M'
,p_date_of_birth => '2-feb-1978');

L_SPECIAL_CEILING_ID := HR_API.G_NUMBER;

hrdpp_update_emp_asg_criteria.insert_batch_lines(

p_batch_id => l_batCH_id
,P_EFFECTIVE_DATE =>trunc(sysdate)
,P_DATETRACK_UPDATE_MODE =>'CORRECTION'
,P_PEOPLE_GROUP_ID => L_PEOPLE_GROUP_ID
,P_SPECIAL_CEILING_STEP_ID => L_SPECIAL_CEILING_ID
,P_ASSIGNMENT_USER_KEY => l_asg_user_key
,P_ORGANIZATION_NAME => 'My Company'
,P_JOB_NAME => 'AA900.Administrative Assistant'
,P_POSITION_NAME => 'AA910.EXECUTIVE ASSISTANT TO CEO'
,P_PAY_BASIS_NAME => 'Exempt'
,P_CON_SEG_USER_NAME => L_P_CON_SEG_USER_NAME);

commit;

end;
at
Labels:

No comments:

Post a Comment

Thanks for your comments submitted.,will review and Post soon! by admin.

Subscribe to: Post Comments (Atom)

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...

  • FND_CANONICAL
    In HRMS we do use date with timestamp for EIT ans SIT segments. To change the format od the date we can use fnd_date . canonical_to_date ...
  • Interview FAQ's-Oracle Apps Technical and Oracle forms
    Oracle Applications Architecture -           Internet computing Architecture is a framework for 3-tired, distributed computing that ...
  • HRMS API with tables
    Converstion API Table Organization hr_organization_api.create_hr_organization hr_all_organization...