Query to retrieve Payroll Run Details for Staff's

/* This Query used to retrieve the Run Result Values for a Single Element or Group of Elements.We can use it for Querying the Information Element also Change the details Accordingly. Eg: How Employee paid by an Element in a Period*/

SELECT ppa.effective_date P_Date,

  papf.employee_number EMP_No,

  papf.person_id PersonID,

  papf.full_name EMP_NAME,

  petf.element_name ElementName,

  pivf.NAME INNAME,

  prrv.result_value VResult

FROM per_all_people_f papf,

  per_all_assignments_f paaf,

  pay_input_values_f pivf,

  pay_run_results prr,

  pay_run_result_values prrv,

  pay_assignment_actions paa,

  pay_payroll_actions ppa,

  pay_element_types_f petf,

  pay_element_classifications pec,

  per_time_periods ptp,

  pay_all_payrolls_f papsf

WHERE papf.person_id         = paaf.person_id

AND pivf.input_value_id      = prrv.input_value_id

AND prr.run_result_id        = prrv.run_result_id

AND paa.assignment_id        = paaf.assignment_id

AND paa.assignment_action_id = prr.assignment_action_id

AND paa.payroll_action_id    = ppa.payroll_action_id

AND pec.classification_id    = petf.classification_id

AND prr.element_type_id      = petf.element_type_id

AND ptp.time_period_id       = ppa.time_period_id

  --AND paaf.business_group_id=9112

AND pec.classification_name IN ('Earnings', 'Involuntary Deductions', 'Involuntary Deductions', 'Pre Social Insurance Deductions', 'Pre-Tax Deductions', 'Tax Deductions', 'Voluntary Deductions', 'Voluntary Deductions', 'Information')

AND ppa.action_type         IN ('R', 'Q')

AND papsf.payroll_id         = paaf.payroll_id

  -- and papf.person_id = 1477

AND ppa.date_earned BETWEEN papsf.effective_start_date AND papsf.effective_end_date

AND ppa.date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date

AND ppa.date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date

AND ppa.date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date

AND ppa.date_earned BETWEEN petf.effective_start_date AND petf.effective_end_date

AND pivf.NAME = 'Pay Value'  /* Add Input Values if needed*/

AND petf.element_name IN ('Basic Salary')  /* Add Elements if needed*/

  --AND TO_CHAR (ppa.effective_date, 'MON-YYYY') = 'MAR-2016'

AND ppa.effective_date BETWEEN '01-JAN-2016' AND '31-OCT-2016';

Vacation Rule in Oracle EBiz

Objective:

• Add Vacation Rule option to User to Set up Vacation Rule for Others.

Step 1:

• Create Custom Menu

1. Routing Rules Administrator
2. Create Routing Rule
3. Update Routing Rule

Step 2:

• Create Custom Responsibility and add the EC Workflow Administrator Menu.

Step 3:

• Go to “Workflow Administrator Web Applications”
• Administrator Workflow > Administration > Select EC Workflow Administrator > Apply

Step 4:

• Add the Responsibility to the User.

Note:

1. Only an Administrator can create Vacation Rule for others. We can set Vacation Rule through the “Workflow Administrator Web Applications” responsibility. 
2. Workflow System Administrator may be a SYSADMIN, Workflow Administrator (Web Admin).
3. Usually SYSADMIN Login have the System administrator and Workflow Administrator Role.
4. If we want to give this rights to Other User, we have to give Admin rights to the User.
5. Workflow System Administrator is EC Workflow Administrator, then a User who holds EC Workflow Administrator can access the Vacation Rule (Set up for Others).
6. Workflow System Administrator is System Administrator, then a User should hold System Administrator Role access the Vacation Rule (Set up for Others).
7. Workflow System Administrator is Workflow Administrator, then a User should hold Workflow Administrator Role access the Vacation Rule (Set up for Others).

Query to find Current Employee List - Oracle HRMS

SELECT A.PERSON_ID,

A.EMPLOYEE_NUMBER,

A.FULL_NAME,

B.ASSIGNMENT_ID,

A.SEX,

A.ORIGINAL_DATE_OF_HIRE,

A.DATE_OF_BIRTH,

A.EMAIL_ADDRESS

FROM PER_ALL_PEOPLE_F A,

PER_ALL_ASSIGNMENTS_F B

WHERE A.PERSON_ID = B.PERSON_ID

AND A.BUSINESS_GROUP_ID = B.BUSINESS_GROUP_ID

AND SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE

AND A.CURRENT_EMPLOYEE_FLAG = 'Y'

--AND A.BUSINESS_GROUP_ID     = 0000

ORDER BY 1;

Edit the Query if you need.

Query to find the Employee and his Leave Details

This Query will fetch the Employee Person id, Employee Number, Employee Name, Leave Start Date, Leave End Date, Leave Name, Employee Assignment Id.

Change the Query as Per your Need.

SELECT A.PERSON_ID PersonId,

B.ASSIGNMENT_ID AssignmentId,

A.EMPLOYEE_NUMBER StaffNumber,

A.FULL_NAME StaffName,

Y.NAME AbsenceName,

Y.ABSENCE_ATTENDANCE_TYPE_ID AbsenceId,

X.DATE_NOTIFICATION NotifyDate,

X.CREATION_DATE CreatedDate,

X.DATE_START LeaveStart,

X.DATE_END LeaveEnd,

X.ABSENCE_DAYS TotalDays

FROM   PER_ABSENCE_ATTENDANCES X,

PER_ABSENCE_ATTENDANCE_TYPES Y,

PER_ALL_PEOPLE_F A,

PER_ALL_ASSIGNMENTS_F B

WHERE   X.ABSENCE_ATTENDANCE_TYPE_ID = Y.ABSENCE_ATTENDANCE_TYPE_ID

AND     X.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID

AND     A.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID

AND     A.PERSON_ID = X.PERSON_ID

AND     A.PERSON_ID = B.PERSON_ID

AND     B.BUSINESS_GROUP_ID = Y.BUSINESS_GROUP_ID

AND     B.PERSON_ID = X.PERSON_ID

AND     SYSDATE BETWEEN A.EFFECTIVE_START_DATE AND  A.EFFECTIVE_END_DATE

AND     SYSDATE BETWEEN B.EFFECTIVE_START_DATE AND  B.EFFECTIVE_END_DATE

AND     X.DATE_START BETWEEN '01-JAN-2016' AND '31-JAN-2017' /* Duration */

AND   A.EMPLOYEE_NUMBER = 'XYZ' /* Employee Number */

AND Y.NAME = 'Unpaid Leave' /* Leave Name */

ORDER BY 6, 8; 

Position Related Useful Links

Position - Doc ID 434046.1

Position Hierarchy - Doc ID 549628.1