How to Handle NULL value in Fast Formula(Returned From Value Set)

 if isnull(ValueSetVariable) = 'N' then 

  (

ValueSetVariable = 0

/* isnull()='N' means Value Set returns 'N' for NULL Value and 'Y' for any values */

  )

Employee Element Entry Query - Fusion Payroll

select
   papf.person_number empnumber,
   ppnf.full_name empname,
   petf.base_element_name salaryelement,
   pivf.base_name inputvalue,
   peevf.screen_entry_value entryvalue,
   to_char(peevf.effective_start_date, 'DD-MON-YYYY') elementstartdate,
   to_char(peevf.effective_end_date, 'DD-MON-YYYY') elementenddate 
from
   pay_element_entries_f peef,
   pay_element_entry_values_f peevf,
   pay_element_types_f petf,
   pay_input_values_f pivf,
   per_all_people_f papf,
   per_all_assignments_f paaf,
   per_person_names_f ppnf 
where
   1 = 1 
   and peef.element_entry_id = peevf.element_entry_id 
   and peef.element_type_id = petf.element_type_id 
   and peevf.element_entry_id = peef.element_entry_id 
   and pivf.input_value_id = peevf.input_value_id 
   and papf.person_id = peef.person_id 
   and papf.person_id = ppnf.person_id 
   and paaf.person_id = papf.person_id 
   and paaf.primary_flag = 'Y' 
   and paaf.assignment_type = 'E' 
   and ppnf.name_type = 'GLOBAL' 
   and pivf.element_type_id = petf.element_type_id 
   and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date) 
   and trunc(sysdate) between trunc(paaf.effective_start_date) and trunc(paaf.effective_end_date) --and petf.base_element_name like ('Basic%') 
order by
   petf.base_element_name,
   papf.person_number asc

Retrieve Element List - Fusion Payroll

Select
   PETF.BASE_ELEMENT_NAME ElementName,
   PEC.BASE_CLASSIFICATION_NAME Classification,
   PETF.PROCESSING_TYPE PTYPE,
   PELF.ELEMENT_LINK_NAME LinkName 
from
   PAY_ELEMENT_TYPES_F PETF,
   PAY_ELE_CLASSIFICATIONS PEC,
   PAY_ELEMENT_LINKS_F PELF 
where
   PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID 
   AND PETF.ELEMENT_TYPE_ID = PELF.ELEMENT_TYPE_ID 
order by
   PEC.BASE_CLASSIFICATION_NAME,
   PETF.BASE_ELEMENT_NAME asc

Current Employee List - Fusion HCM

SELECT
   PAPF.PERSON_ID,
   PAAF.ASSIGNMENT_ID ASSIGNMENT_ID,
   PAAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER,
   PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
   PPNF.FULL_NAME EMPLOYEE_NAME,
   PAPF.START_DATE HIRE_DATE,
   HLE.NAME LEGAL_EMPLOYER,
   FAB.BU_NAME BU_NAME,
   PAAF.ASSIGNMENT_STATUS_TYPE STATUS 
FROM
   PER_ALL_PEOPLE_F PAPF,
   PER_PERSON_NAMES_F PPNF,
   PER_ALL_ASSIGNMENTS_F PAAF,
   HR_LEGAL_ENTITIES HLE,
   FUN_ALL_BUSINESS_UNITS_V FAB 
WHERE
   PAPF.PERSON_ID = PAAF.PERSON_ID 
   AND PAPF.PERSON_ID = PPNF.PERSON_ID 
   AND PAAF.BUSINESS_UNIT_ID = FAB.BU_ID 
   AND PAAF.PRIMARY_FLAG = 'Y' 
   AND PAAF.ASSIGNMENT_TYPE = 'E' 
   AND PPNF.NAME_TYPE = 'GLOBAL' 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PAAF.EFFECTIVE_START_DATE) AND TRUNC(PAAF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF.EFFECTIVE_START_DATE) AND TRUNC(PPNF.EFFECTIVE_END_DATE) 
   AND TRUNC(SYSDATE) BETWEEN TRUNC(HLE.EFFECTIVE_START_DATE) AND TRUNC(HLE.EFFECTIVE_END_DATE) 
   AND PAAF.LEGAL_ENTITY_ID = HLE.ORGANIZATION_ID 
   AND HLE.CLASSIFICATION_CODE = 'HCM_PSU' 
   AND PAAF.ASSIGNMENT_STATUS_TYPE = 'ACTIVE' 
ORDER BY
   4

Retrieve Payroll Results in Oracle Fusion Payroll

Select
   ppa.effective_date PayrollDate,
   papf.Person_Number PersonNumber,
   PETF.BASE_ELEMENT_NAME ElementName,
   prrv.RESULT_VALUE PayAmount 
from
   PAY_RUN_RESULTS prr,
   PAY_RUN_RESULT_VALUES prrv,
   PAY_ELEMENT_TYPES_F PETF,
   PAY_ELE_CLASSIFICATIONS PEC,
   PAY_INPUT_VALUES_F pivf,
   PAY_PAYROLL_REL_ACTIONS PRREA,
   PAY_PAYROLL_ACTIONS PPA,
   PAY_PAY_RELATIONSHIPS_DN PPRDN,
   PAY_ALL_PAYROLLS_F papsf,
   per_all_people_f papf 
where
   prr.RUN_RESULT_ID = prrv.RUN_RESULT_ID( + ) 
   and prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID 
   AND PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID 
   and pivf.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID 

   and PRREA.RETRO_COMPONENT_ID is Null
   and pivf.ELEMENT_TYPE_ID = prr.ELEMENT_TYPE_ID 
   and pivf.INPUT_VALUE_ID = prrv.INPUT_VALUE_ID( + ) 
   AND pivf.BASE_NAME = 'Pay Value'  --and papsf.PAYROLL_NAME like '%Junior%'
   and PRREA.PAYROLL_REL_ACTION_ID = PRR.PAYROLL_REL_ACTION_ID 
   and PRREA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID 
   and PPA.ACTION_TYPE in 
   (
      'R',
      'Q'
   )
   and PRREA.PAYROLL_RELATIONSHIP_ID = PPRDN.PAYROLL_RELATIONSHIP_ID 
   and papsf.PAYROLL_ID = ppa.PAYROLL_ID 
   and PPRDN.PERSON_ID = Papf.PERSON_ID 
   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 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 TO_CHAR (ppa.effective_date, 'DD-MM-YYYY') = '31-08-2018'  --and papf.Person_Number = ''
order by
   papf.Person_Number asc

Retrieve Employee Salary History in EBiz

SELECT
  employee_number,
  proposed_salary_n,
  from_date,
  CASE
    WHEN
      to_date IS NULL 
    THEN
      to_date('31-dec-4712') 
    ELSE
      to_date 
  END
  xx 
FROM
  (
    SELECT
      a.employee_number,
      c.proposed_salary_n,
      c.change_date from_date,
      (
        LEAD (c.change_date, 1) OVER (PARTITION BY a.employee_number 
      ORDER BY
        c.change_date)
      )
       - 1 to_date 
    FROM
      per_all_people_f a,
      per_all_assignments_f b,
      per_pay_proposals c 
    WHERE
      1 = 1 
      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.person_id = b.person_id    --and a.employee_number = '001'
      AND b.assignment_id = c.assignment_id 
    ORDER BY
      a.employee_number,
      c.change_date
  )
GROUP BY
  employee_number,
  proposed_salary_n,
  from_date,
  to_date 
ORDER BY
  1,
  3;

PMS Doc Id and My Learnings

Doc Id's:

Appraisee Update Action Link From The Notification Results into an Error Page (Doc ID 2047250.1)

Information Center: EBS Performance Management (All Application Versions) (Doc ID 1297851.2)

How to Enable Personalization link for OAF Pages?

To enable Personalization, Set the Profile

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

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

Oracle HRMS - Useful Links

User Profile Options

https://eqextportal.qantas.com.au/OA_HTML/help/state/content/navId.2/navSetId.iHelp/vtAnchor.profiles/vtTopicFile.iHelp%7CHelpServlet%7CUS%7Cper%7C%40profiles/

Person-Name Formats

https://eqextportal.qantas.com.au/OA_HTML/help/state/content/navId.2/navSetId.iHelp/vtAnchor.profiles/vtTopicFile.iHelp%7CHelpServlet%7CUS%7Cper%7C%40profiles/

How to Process Payroll after Termination - Oracle EBiz

1. Set Termination Rule to Last Standard Process for elements process on Normal Payroll Run.
2. Set Termination Rule to Final Process for elements process on Late Payroll Run.
3. Terminate the Employee and fill the Final Process Date as per your requirement. Ensure HR      system status of Terminate Assignment.
4. Actual Date is the Employees  actual date of Termination, all the regular Payroll Elements will be end dated.
5. Final Process date is the date where your process Final settlement, Late Payroll Run Elements will be processed.
6. Eg. If an Employee Terminated on 25-May-2016, his Actual Date is 25-May-2016, LSP Date is 31-May-2016. If you give Final process date as 30-June-2016, you can run Elements(Final Process Elements- Refer Point 2) till 30-June-2016. The assignment screen can be opened and can run Quick Pay or Payroll for the employee till 30-June-2016. Later 30-June-2016 you can't open the Assignment screen of an Employee.

Salary Administration

Useful Oracle Metalink Id for Salary Administration

142548.1

392567.1

Key Flex Field to Database items - Oracle Hrms

We can convert the Key Flex Field into a Database items by running the concurrent

"Create Key Flexfield DB Items"

Oracle R12 Payroll Setup Steps

1. Complete Core HR Setup
2. Define Payment Type(Optional)
3. Define Payment Method
4. Define Payroll
5. Element Creation
6. Element Link and Costing
7. Define Salary Basis
8. Write Formula(Fast Formula)
9. Formula Result
10. Quickpay or Payroll Run

*Concepts includes Retropay, Balances.

Steps to Create a Request in Employee Self Service

Create SIT
Create Function
Add Function in Employee Self Service Menu and Global Self Service Menu
Personalize SIT Flex Field
Workflow
AME for Approval

Oracle R12 Core Hr Setup Steps

1. Login with Sysadmin Responsibility
2. Responsibility Creation(Custom)
3. User Creation and Attach Corresponding Responsibilities
4. Key Flex Field Creation 
1. Job
2. Position
3. Grade
4. Compentency
5. People Group
6. Cost Allocation
5. Value Sets Creation
6. Values Creation
7. Location
8. Business Group & Business Group Info
9. Profile Option 
1. HR Business Group
2. HR Security
3. HR User
10. Organization Creation & Organization Hierarchy
11. Job 
12. Position & Position Hierarchy
13. Grade & Grade Rate
14. Hire an Employee (Enter & Maintain)
15. Assign Assignment(Job, Position, Grade, People Group)