Oracle Time and Labor Release Notes for Release 12.2.6

Oracle HRMS for Saudi Arabia Release Notes for Release 12.2.6

Oracle BI Publisher for Beginners Guide

Query to get Pending Leave Request in Oracle Self Service

/*****Open Leave Requests In - Progress Workflows Oracle SSHR*****/

select papf.employee_number "Emp No.",
       papf.full_name "Employee Name",
       wf.status,
       paat.name "Leave Type",
       hrs.information1 "Start Date",
       hrs.information2 "End Date",
       DECODE(hrs.information5,80,hrs.information7 ||' Hrs',hrs.information8 || ' Days') "Duration",
       wf.begin_date "Applied on",
       wf.due_date "Due Date",
       wf.from_user "Last Approver",
       fnd.user_name,
       wf.to_user "Pending With",
       wf.subject,
       wf.from_role,
       wf.recipient_role,
       wf.original_recipient,
       hra.item_key     
  from wf_notifications             wf,
       hr_api_transactions          hra,
       hr_api_transaction_steps     hrs,
       per_absence_attendance_types paat,
       fnd_user                     fnd,
       per_people_x                 papf
where wf.item_key = hra.item_key
   and hrs.transaction_id = hra.transaction_id
   and paat.absence_attendance_type_id = hrs.information5
   AND fnd.employee_id = hra.creator_person_id
   AND papf.PERSON_ID = fnd.employee_id
   AND WF.MESSAGE_TYPE = 'HRSSA'
   AND WF.MESSAGE_TYPE = 'HRSSA'
   AND WF.STATUS IN ('OPEN', 'CANCELLED')
   AND Wf.subject like '%Leave%'
   AND wf.subject not like 'Application%Error%'
   AND wf.from_user not like 'SYSADMIN'
order by wf.begin_date

Query to get employee payment method in oracle hrms

SELECT pap.employee_number, pap.full_name, pap.person_id, paf.assignment_id,
       pam.org_payment_method_name, pay.percentage, pay.priority,
       pea.segment1 bank_name, pea.segment2 branch, pea.segment3 account_type,
       pea.segment4 iban, pea.segment5 ACCOUNT
  FROM pay_personal_payment_methods_f pay,
       pay_org_payment_methods_f pam,
       pay_external_accounts pea,
       per_all_assignments_f paf,
       per_all_people_f pap
 -- Hr_lookups hrl
WHERE  pay.org_payment_method_id = pam.org_payment_method_id
   AND paf.assignment_id = pay.assignment_id
   AND paf.person_id = pap.person_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN pay.effective_start_date AND pay.effective_end_date
   AND TRUNC (SYSDATE) BETWEEN pam.effective_start_date AND pam.effective_end_date
   AND paf.primary_flag = 'Y'
   AND pea.external_account_id = pay.external_account_id

Query to get Employee Payroll Cost

SELECT per.employee_number,
       per.full_name,
       petf.element_name,
       DECODE (pc.debit_or_credit,  'D', 'Debit',  'C', 'Credit',  NULL)
          debit_or_credit,
       pc.costed_value amount,
       pcak.segment4 account,
       ffvt.description account_description,
       ppa.effective_date
  FROM pay_costs pc,
       pay_assignment_actions pac,
       pay_payroll_actions ppa,
       per_all_assignments_f paaf,
       per_all_people_f per,
       pay_run_results prr,
       pay_element_types_f petf,
       pay_cost_allocation_keyflex pcak,
       fnd_flex_value_sets ffvs,
       fnd_flex_values ffv,
       fnd_flex_values_tl ffvt
 WHERE     pac.assignment_action_id = pc.assignment_action_id
       AND paaf.assignment_id = pac.assignment_id
       AND pac.payroll_action_id = ppa.payroll_action_id
       AND paaf.person_id = per.person_id
       AND paaf.assignment_type = 'E'
       AND paaf.assignment_status_type_id = 1
       AND paaf.primary_flag = 'Y'
       AND ppa.effective_date BETWEEN per.effective_start_date
                                  AND per.effective_end_date
       AND ppa.effective_date BETWEEN paaf.effective_start_date
                                  AND paaf.effective_end_date
       AND ppa.effective_date BETWEEN :l_date_from AND :l_date_to
       AND pc.run_result_id = prr.run_result_id
       AND petf.element_type_id = prr.element_type_id
       AND per.employee_number = nvl(:l_employee_number,per.employee_number)
       AND pcak.cost_allocation_keyflex_id = pc.cost_allocation_keyflex_id
       AND flex_value_set_name = 'XXX_FIN_ACC_VS' ---enter your value set name
       AND ffv.flex_value_set_id = ffvs.flex_value_set_id
       AND ffvt.flex_value_id = ffv.flex_value_id
       AND ffvs.flex_value_set_id = ffv.flex_value_set_id
       AND ffvt.language = 'US'
       AND ffv.enabled_flag = 'Y'
       AND pcak.segment4 = ffv.flex_value

Employee Termination Query

SELECT   pap.employee_number, pap.full_name, pap.start_date hire_date,
         ps.actual_termination_date, ps.leaving_reason
    FROM per_all_assignments_f paf,
         per_periods_of_service ps,
         per_all_people_f pap,
         per_person_types ppt
   WHERE paf.period_of_service_id = ps.period_of_service_id
     AND ps.actual_termination_date IS NOT NULL
     AND pap.person_id = paf.person_id
     AND pap.person_type_id = ppt.person_type_id
     AND ps.actual_termination_date BETWEEN pap.effective_start_date
                                        AND pap.effective_end_date
     AND ps.actual_termination_date BETWEEN paf.effective_start_date
                                        AND paf.effective_end_date
     AND ps.actual_termination_date >= &Termination_date
     AND ps.leaving_reason = &reason
ORDER BY pap.employee_number, ps.actual_termination_date

Employee Wise HR Query for Saudi

SELECT   pap.employee_number "Employee No.", pap.full_name "Full Name",
         NVL (pap.national_identifier,
              ppe.pei_information1) "Iqama/Id Number",
         hlk.meaning "Nationality", hsc.segment2 "GOSI Number",
         ppa.proposed_salary_n "Basic Salary",
         TO_CHAR (pap.start_date, 'DD-MON-YYYY') "Hire Date",
         TO_CHAR (pap.date_of_birth, 'DD-MON-YYYY') "Date Of Birth",
         pap.per_information8 "AR Date of Birth",
         pap.per_information9 "AR Hire Date", pj.NAME "Job",
         pp.NAME "Position", pj.NAME "Grade", hao.NAME "Department",
         ppg.group_name "People Group",
         ppe.pei_information2 "Iqama Profession",
         ppe.pei_information4 "AR Iqama Exp Date",
         ppe.pei_information7 "ENG Iqama Exp Date",
         ppe1.pei_information1 "Passport No",
         ppe1.pei_information4 "Passport Exp Date",
         ppe1.pei_information5 "Passport Issue Place"
       
    FROM hr.per_all_people_f pap,
         hr.per_all_assignments_f paf,
         hr.hr_all_organization_units hao,
         per_pay_proposals ppa,
         hr.per_jobs pj,
         per_positions pp,
         hr.per_grades pg,
         pay_people_groups ppg,
         hr.hr_locations_all hla,
         hr_soft_coding_keyflex hsc,
         apps.hr_lookups hlk,
         per_people_extra_info ppe,
         per_people_extra_info ppe1
   WHERE paf.organization_id = hao.organization_id
     AND paf.job_id = pj.job_id
     AND pap.person_id = paf.person_id
     AND paf.location_id = hla.location_id
     AND pap.person_id = ppe.person_id(+)
     AND ppe.information_type(+) = 'SA_IQAMA'
      AND pap.person_id = ppe1.person_id(+)
     AND ppe1.information_type(+) = 'SA_PASSPORT'
     AND paf.people_group_id = ppg.people_group_id(+)
     AND hsc.segment1 = hao.business_group_id
     AND hsc.soft_coding_keyflex_id(+) = paf.soft_coding_keyflex_id
     AND paf.grade_id = pg.grade_id(+)
     AND paf.assignment_id = ppa.assignment_id
     AND paf.position_id = pp.position_id(+)
     AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
                             AND pap.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date
                             AND paf.effective_end_date
     AND hlk.lookup_type = 'NATIONALITY'
     AND hlk.lookup_code(+) = pap.nationality
ORDER BY TO_NUMBER (pap.employee_number)

OAF Personalization Document

Oracle HRMS Saudi Arabia Localization Setup Document