IMPORTANT TABLES IN HRMS
per_all_people_f:
Tracks Employee Records.
Date track table with primary key person_id.
Can get personal ,professional,taxable location and other informations of the person(employee).
per_all_assignments:
Central table of oracle payroll.
Element entries are stored against assignment record.
Assignment_id is the primary key.
Keeps track of employee position,grade etc.
per_person_type:
Master table of person types(casual,applicant,employees).
person_type_id is the primary key.
Per_periods_of_service:
Period of service details for an employee.
----------------------------------------------------------------------------------------
PAYROLL
pay_element_types_f:
list payroll elements
element_type_id with date track columns are the primary keys.
pay_element_links_f:
Payroll linked elements.
After linking payroll elements to the payroll it gets into employee payroll.
element_link_id with date track columns are the primary keys.
pay_element_entries_f:
Elements attached to an assignment has an entry in this table
.
pay_payroll_actions:
Type of pay and costing sements can be driven out.
BEE(BATCH ELEMENT ENTRIES)
BEE(Batch Element Entries)
Bulk of data added for many exisiting employees.
May be accounting,DFF,KFF elements added to numerous employees at a same time is referred to BEE
Process is as follows:
Example:To add new bonus element for exisiting employees
1.Create a control file with the columns required eg:name of the element,value given to the element
2.Create an table to store the vales to updload the data .
3.Use FNDLOAD for file upload
Example:
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
**Refer Metalink or Oracle Applications Systems Administrator Guide
4.Create the Batch Header and Batch lines for its respective tables using API's(pay_batch_header.create_batch_header).
Tables to be Populated:Pay_batch_headers,pay_batch_lines
Batch header consist of an identical name for the batch to be uploaded(batch_name,batch_id).
Batch lines consist of values to bonus elements(batch_id).
5.Check the Status of file uploaded in the batch Status.
it will be U-unprocesses,V-Valid,P-processed.
Bulk of data added for many exisiting employees.
May be accounting,DFF,KFF elements added to numerous employees at a same time is referred to BEE
Process is as follows:
Example:To add new bonus element for exisiting employees
1.Create a control file with the columns required eg:name of the element,value given to the element
2.Create an table to store the vales to updload the data .
3.Use FNDLOAD for file upload
Example:
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter
**Refer Metalink or Oracle Applications Systems Administrator Guide
4.Create the Batch Header and Batch lines for its respective tables using API's(pay_batch_header.create_batch_header).
Tables to be Populated:Pay_batch_headers,pay_batch_lines
Batch header consist of an identical name for the batch to be uploaded(batch_name,batch_id).
Batch lines consist of values to bonus elements(batch_id).
5.Check the Status of file uploaded in the batch Status.
it will be U-unprocesses,V-Valid,P-processed.
CONVERSTIONS USING API
hr_person_address_api is used for example purpose.
DECLARE
x_business_group_id NUMBER;
x_person_id NUMBER;
x_address_id NUMBER;
x_obj_no NUMBER;
x_errm VARCHAR2(100);
CURSOR legacy_address_cursor IS
/*replace the hard-coding with your source data*/
Lets say you wish to migrate a person address record of following data in Oracle HRMS
Address Line 1 : Martket Stree
Address Line 2 : London
Date FROM : SYSDATE - 1
Employee number : 90909090
Person Id : 134593
SELECT '90909090' AS employee_number
,'Woodlands Street' AS addr_line1
,'London' AS addr_line2
,'SW1 1DB' AS post_code
,'07968875963' AS tel_no
,134593 AS person_id
/*as you have already migrated this person*/
,trunc(SYSDATE) - 1 date_from
/*you can make this to be the same as start date of person*/
FROM dual
--replace this with your actual source table
;
BEGIN
SELECT business_group_id
INTO x_business_group_id
FROM per_business_groups
WHERE NAME = 'Your Buss Group Name here or Setup Business Group';
FOR p_record IN legacy_address_cursor
LOOP
BEGIN
hr_person_address_api.create_gb_person_address(p_validate => FALSE
,p_effective_date => SYSDATE
,p_pradd_ovlapval_override => FALSE
,p_person_id => p_record.person_id
,p_primary_flag => 'Y'
,p_date_from => p_record.date_from
,p_address_line1 => initcap(p_record.addr_line1)
,p_address_line2 => initcap(p_record.addr_line2)
,p_postcode => p_record.post_code
,p_country => 'GB'
,p_telephone_number => p_record.tel_no
,p_address_id => x_address_id
,p_object_version_number => x_obj_no
,p_date_to => NULL
,p_address_type => NULL
,p_comments => NULL);
dbms_output.put_line('Address for person_id=>'p_record.person_id' has been loaded');
--update the legacy source address table to change migration status of record
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Error when migrating Address for person_id=>' p_record.person_id ' 'x_errm);
/* log_error(p_record.person_id,x_errm);*/
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Fatal Error ' x_errm);
/* log_error(-1,'Fatal Error ' x_errm);*/
END;
/
staging table is a table where data is held temporarily for transformation, enrichment and validation before it is moved to the final destination. Staging tables are typically used for conversion and interfaces.
Transformation includes activities such as changing formats and data types, e.g. the character string "23-SEP-2006" into the date September 23 2006; mapping one value to another, e.g. from 1 to "M".
Enrichment is adding new data to the records that were not available in the source.
Validation is checking that the values in fields in a record are valid and that the record is internally consistent.
DECLARE
x_business_group_id NUMBER;
x_person_id NUMBER;
x_address_id NUMBER;
x_obj_no NUMBER;
x_errm VARCHAR2(100);
CURSOR legacy_address_cursor IS
/*replace the hard-coding with your source data*/
Lets say you wish to migrate a person address record of following data in Oracle HRMS
Address Line 1 : Martket Stree
Address Line 2 : London
Date FROM : SYSDATE - 1
Employee number : 90909090
Person Id : 134593
SELECT '90909090' AS employee_number
,'Woodlands Street' AS addr_line1
,'London' AS addr_line2
,'SW1 1DB' AS post_code
,'07968875963' AS tel_no
,134593 AS person_id
/*as you have already migrated this person*/
,trunc(SYSDATE) - 1 date_from
/*you can make this to be the same as start date of person*/
FROM dual
--replace this with your actual source table
;
BEGIN
SELECT business_group_id
INTO x_business_group_id
FROM per_business_groups
WHERE NAME = 'Your Buss Group Name here or Setup Business Group';
FOR p_record IN legacy_address_cursor
LOOP
BEGIN
hr_person_address_api.create_gb_person_address(p_validate => FALSE
,p_effective_date => SYSDATE
,p_pradd_ovlapval_override => FALSE
,p_person_id => p_record.person_id
,p_primary_flag => 'Y'
,p_date_from => p_record.date_from
,p_address_line1 => initcap(p_record.addr_line1)
,p_address_line2 => initcap(p_record.addr_line2)
,p_postcode => p_record.post_code
,p_country => 'GB'
,p_telephone_number => p_record.tel_no
,p_address_id => x_address_id
,p_object_version_number => x_obj_no
,p_date_to => NULL
,p_address_type => NULL
,p_comments => NULL);
dbms_output.put_line('Address for person_id=>'p_record.person_id' has been loaded');
--update the legacy source address table to change migration status of record
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Error when migrating Address for person_id=>' p_record.person_id ' 'x_errm);
/* log_error(p_record.person_id,x_errm);*/
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
x_errm := SQLERRM;
dbms_output.put_line('Fatal Error ' x_errm);
/* log_error(-1,'Fatal Error ' x_errm);*/
END;
/
staging table is a table where data is held temporarily for transformation, enrichment and validation before it is moved to the final destination. Staging tables are typically used for conversion and interfaces.
Transformation includes activities such as changing formats and data types, e.g. the character string "23-SEP-2006" into the date September 23 2006; mapping one value to another, e.g. from 1 to "M".
Enrichment is adding new data to the records that were not available in the source.
Validation is checking that the values in fields in a record are valid and that the record is internally consistent.