How to create a report for various payroll runs

SELECT < Columns>
from pay_payroll_actions ppa,
pay_all_payrolls_f papf,
per_time_periods ptp
where papf.payroll_id = ppa.payroll_id
and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
and ppa.action_type IN ('Q','R')
and ptp.payroll_id = papf.payroll_id
and papf.payroll_name = :pPayrollName
and ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
and :pDate BETWEEN ptp.start_date AND ptp.end_date

Pass Any Date and Payroll Name. The query should list down all payroll runs in the period in which the date falls.

Read More

Interface from OTL to 3rd party payroll provider

You can use and find the details using following tables

HXC_TIMECARD_SUMMARY
HXC_TIME_BUILDING_BLOCKS
HXC_TIME_ATTRIBUTES
HXC_TIME_ATTRIBUTE_USAGES


Your 3rd party payroll provider should be giving you the specification for what data they are looking for

Read More

Query : To get Active Employees and Terminated Employees

SELECT
PAAF.ASSIGNMENT_ID,
PAPF.PERSON_ID AS PERSON_ID2,
PAPF.FIRST_NAME AS FIRST_NAME,
PAPF.LAST_NAME AS LAST_NAME,
PAPF.EMAIL_ADDRESS AS EMAIL_ADDRESS,
TO_CHAR(PPS.ACTUAL_TERMINATION_DATE) AS ACTUAL_TERMINATION_DATE,
TO_CHAR(PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE,
PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER,
TO_CHAR(PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE,
PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID,
PAAF.SUPERVISOR_ID AS SUPERVISOR_ID ,
PAPF.LAST_UPDATE_DATE papf_update_date,
PAAF.LAST_UPDATE_DATE paaf_update_date,
ppt.user_person_type
FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERIODS_OF_SERVICE PPS,
hr.per_person_type_usages_f pptu,
hr.per_person_types ppt
WHERE PAAF.PERSON_ID = PAPF.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y'
--AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
and paaf.assignment_type != 'B' --conditional
and pptu.effective_start_date between
papf.effective_start_date and papf.effective_end_date
and papf.person_id = pptu.person_id
and papf.person_type_id = pptu.person_type_id
and pptu.person_type_id = ppt.person_type_id
and papf.person_type_id = ppt.person_type_id
and papf.business_group_id = ppt.business_group_id
AND PAAF.period_of_service_id = PPS.period_of_service_id
and papf.person_id = pps.person_id
AND (
(ppt.user_person_type like 'Ex-employee%'
and PPS.ACTUAL_TERMINATION_DATE between paaf.effective_start_date and PAAF.EFFECTIVE_END_DATE)
or
( ppt.user_person_type like 'Employee%'
and trunc(sysdate) between paaf.effective_start_date and PAAF.EFFECTIVE_END_DATE )
)
AND (
(ppt.user_person_type like 'Ex-employee%'
and PPS.ACTUAL_TERMINATION_DATE between papf.effective_start_date and PAPF.EFFECTIVE_END_DATE)
or
(ppt.user_person_type like 'Employee%' and
trunc(sysdate) between papf.effective_start_date and PAPF.EFFECTIVE_END_DATE )
)

Read More

INFO: EMPLOYEE_CATEGORY holds in the table *PER_ALL_ASSIGNMENTS_F

Employee Category is a lookup. the value that is in per_all_assignments_f for employee_category is the lookup_code that is stored.
FND_COMMON_LOOKUPS has the column lookup_type with value 'EMP_CAT' and column lookup_code is the value in per_all_assignments_f
and meaning is value seen in the front end. query the table fnd_common_lookups where lookup_type = 'EMP_CAT'
and you will understand. you can also query the quickcodes window from hrms resp in front end for 'employment category' value.

EMPLOYEE_CATEGORY holds in the table *PER_ALL_ASSIGNMENTS_F

Read More

Query : Job Change Date

select papf.employee_number,max(oldpaaf.effective_end_date +1) "Job Change Date"
from per_all_people_f papf,
per_all_assignments_f oldpaaf,
per_all_assignments_f newpaaf
where papf.person_id = oldpaaf.person_id
and papf.person_id = newpaaf.person_id
and oldpaaf.job_id != newpaaf.job_id
and oldpaaf.effective_end_date < newpaaf.effective_start_date
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between newpaaf.effective_start_date and newpaaf.effective_end_date
group by papf.employee_number

Read More

API to use to update employee additional attributes

Use this API
HR_PERSON_API.UPDATE_US_PERSON to update attribute columns

Read More

How to Purge Employee Record

In two ways this can be done.

1. US HRMS Manager -> People -> Delete Personal Records. Query the person and click delete (red cross in the main menu).
2. Call the API HR_PERSON_API.DELETE_PERSON with appropriate parameters.

set the date one day before effective date, and than delete the person record. Assumtion: The person should not have any absences. Firts delete it, dann delete the perosn record.

Read More