Query to get Element Advance Salary in Oracle APPS HRMS

SELECT papf.employee_number

      ,prrv.result_value

  FROM pay_run_results prr

  ,pay_element_types_f petf

  ,pay_run_result_values prrv

  ,pay_input_values_f piv

  ,pay_assignment_actions paa

  ,pay_payroll_actions ppa

  ,per_all_assignments_f paaf

  ,per_all_people_f papf

 WHERE prr.element_type_id = petf.element_type_id

   AND prr.run_result_id = prrv.run_result_id

   AND prrv.input_value_id = piv.input_value_id

   AND prr.assignment_action_id = paa.assignment_action_id

   AND paaf.assignment_id = paa.assignment_id

   AND paaf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   and paaf.person_id= papf.person_id

   AND papf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   AND upper(petf.element_name) = 'ADVANCE SALARY'

   AND piv.name = 'Pay Value' 

   AND SYSDATE BETWEEN piv.effective_start_date AND  piv.effective_end_date

   AND SYSDATE BETWEEN petf.effective_start_date AND  petf.effective_end_date

   AND SYSDATE BETWEEN paaf.effective_start_date AND  paaf.effective_end_date

   AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date

   AND ppa.payroll_action_id = paa.payroll_action_id                         

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

   AND ppa.effective_date = '31-JUL-2022'

   AND prrv.result_value > '0'

Consolidation Set in Oracle Payroll

• A Consolidation set is a collection of payrolls. You can group payrolls using these sets. 
• It helps in processing multiple payrolls at once. 
• If you wish to run two different payrolls on a particular date, you can just run the payroll process on the consolidation set having those two payrolls.

Different Formulas used in Oracle Payroll

There are many different types of formulas in Oracle Payroll. Few of them are:

• Accrual Formulae
• Accrual
• Accrual Carryover
• Accrual Ineligibility
• Accrual Sub Formula
• Oracle Payroll
• Element Skip
• Element Input Validation
• Element Type and Input Value determination
• Payroll Run Proration

What are Pre Payments in Oracle Payroll

• Pre-Payments is a process with which, the payroll system allots the payroll money in to the different personal payment methods of each employee. 
• Pre Payment is run as part of the post payroll process. 
• Run the prepayments with an override payment method. 

What are Recurring/Non Recurring Elements in Oracle Payroll

Elements can be divided into two types, based on the processing type.

1. Recurring: A Recurring Element, once attached to a person, gets added once for each pay period automatically. 
2. Non Recurring: A Non recurring element, once attached to a person continues for the related pay period only, and expires i.e. end dated after that period.

Payroll Interview Questions

• What are the mandatory fields while creating Payroll?
• Payroll Name
• Period Type
• Start Date
• Define Elements?
• Elements are the building blocks for earnings, deductions etc. of a Payroll.
• It is a Data Structure which is used to hold information for both Human Resources and Payroll.
• In Human Resources elements may represents compensation types including Earnings such as Salary, Hourly Wages and Bonuses.
• In Payroll, elements constitute all the items in the Payroll run process.
• What the Element can represent?
• Earnings >> Salary, Wages and Bonuses.
• Benefits >> Employee stock and Pension Plans.
• Non-Payroll Items >> Expenses
• Absences from work
• Voluntary and In-Voluntary deductions
• Employer Taxes and other Liabilities.
• What are the pre-defined Elements?
• UK Payroll legislation provides many predefined elements
• Tax
• National Insurance (Employee/Employer)
• Court Orders
• These Elements cannot be modified.
• What are the Element Entry Concepts?
• Recurring ->Recurring Entries can exists over many Payroll periods
• Non-Recurring -> Non-Recurring Entries are valid for single Payroll period only.
• What are the types of Element Entry?
• There are 4 types
• Normal Entry
• Override Entry
• Additional Entry
• Adjustment Entry
• Additive Adjustment
• Replacement Adjustment
• Balance Adjustment
• How can we add a new input value to an existing Element?
• We can add an additional input values to an existing Element if the element has not been processed in a Payroll run and the Effective data is the same date of creation of the Element.
• What is Salary Basis ?
• Salary basis is the duration on which the salary is reckoned. It is a rule to administer pay.
• Define Quick Pay and Batch Processing?
• When calculating pay, we can use either Quick Pay or Batch Process.
• Quick Pay Process-->This is for one employee
• Batch Process --> This is for all employees together
• What is Quick Pay/ Advance Pay/ Retro Pay?
• QuickPay: 
• QuickPay enables you to carry out payroll processing for individual employees. 
• You can use QuickPay to pay employees who are leaving and who require payment immediately.
• If an employee asks what their net pay will be this month, you can run QuickPay to find the answer, then roll it back to remove all results from the database.
• Advance Pay:
• The Advance Pay process enables you to pay employees in advance for holidays or other events. 
• The process performs payroll runs for the periods to be advanced, using all date effective information in place, and stores the final net figure as the amount to be advanced. 
• Retro Pay:
• Payment for any previous cycle in the current payroll run
• Define Batch Element Entry?
• This is an open interface specially designed for elements. 
• With BEE we can quickly incorporate mass updates in any specific elements.

Query to get Oracle Payroll Net Pay

   SELECT paf.assignment_number

        ,ppf.full_name

,paygr.payroll_name

,paygr.payroll_id

,element_name

,pbt.balance_name

,ppa.effective_date

,prv.result_value

    FROM pay_element_types_f pet

    ,pay_input_values_f piv

,pay_run_result_values prv

,pay_run_results prr

,pay_assignment_actions paa

,pay_payroll_actions ppa

,pay_balance_types pbt

,pay_balance_feeds_f pbff

,per_people_f ppf

,per_assignments_f paf

,per_grades gr

,pay_all_payrolls_f paygr

   WHERE prr.element_type_id = pet.element_type_id

     AND piv.element_type_id = pet.element_type_id

     AND prv.input_value_id = piv.input_value_id

     AND prv.run_result_id = prr.run_result_id

     AND prr.assignment_action_id = paa.assignment_action_id

     AND paa.payroll_action_id = ppa.payroll_action_id

     AND pbff.balance_type_id = pbt.balance_type_id

     AND piv.input_value_id = pbff.input_value_id

     AND ppa.effective_date BETWEEN :P_FROM_DATE AND :P_TO_DATE

     AND ppf.person_id = paf.person_id

     AND SYSDATE BETWEEN ppf.effective_start_date

                     AND ppf.effective_end_date

     AND paf.effective_start_date =

                (SELECT MAX(effective_start_date)

                   FROM per_assignments_f paf1

                  WHERE paf.assignment_id = paf1.assignment_id)

     AND paa.assignment_id = paf.assignment_id

     AND gr.grade_id = paf.grade_id

     AND paygr.payroll_id = paf.payroll_id

     AND SYSDATE BETWEEN paygr.effective_start_date

                     AND paygr.effective_end_date

Payroll Interview Questions in Oracle Apps

• What are the mandatory fields while creating Payroll?
• Payroll Name
• Period Type
• Start Date

• Define Elements?
• Elements are the building blocks for earnings, deductions, etc. of a Payroll. 
• It is a Data Structure which is used to hold information for both Human Resources and Payroll.
• In Human Resources elements may represents compensation types including Earnings such as Salary, Hourly Wages and Bonuses.
• In Payroll, elements constitute all the items in the Payroll run process.

• What the Element can represent?
• Earnings such as Salary, Wages & Bonuses
• Benefits such as employee stock & pension plans
• Non-Payroll items such as Expenses
• Employer Taxes and other Liabilities.
• Absences from work
• Voluntary and In-Voluntary deductions

• What are the Element Entry Concepts?
• Recurring: Entries can exists over many Payroll periods
• Non-Recurring: Entries are valid for single Payroll period only.

• What are the types of Element Entry?
• There are four types
1. Normal Entry
2. Override Entry
3. Additional Entry
4. Adjustment Entry
• Additive Adjustment
• Replacement Adjustment
• Balance Adjustment

• How can we add a new input value to an existing Element?
• We can add an additional input values to an existing Element if the element has not been processed in a Payroll run and  the Effective data is the same date of creation of the Element.

• What is Salary Basis ?
• Salary basis is the duration on which the salary is reckoned. It is a rule to administer pay.

• Define Quick Pay & Batch Processing?
• When calculating pay, we can use either Quick Pay Process or Batch Process
• Quick Pay Process which is for one employee.
• Batch Process for all employees together.

• What is Quick Pay/ Advance Pay/ Retro Pay?
• QuickPay: 
• QuickPay enables you to carry out payroll processing for individual employees. 
• You can use QuickPay to pay employees who are leaving and who require payment immediately.
• If an employee asks what their net pay will be this month, you can run QuickPay to find the answer, then roll it back to remove all results from the database.

• Advance Pay:
• The Advance Pay process enables you to pay employees in advance for holidays or other events. 
• The process performs payroll runs for the periods to be advanced, using all date effective information in place, and stores the final net figure as the amount to be advanced.

• Retro Pay: 
• Payment for any previous cycle in the current payroll run

• What is Retro Pay, Advance Pay & Absence Pay?
• Retro Pay: Payment for any previous cycle in the current payroll run
• Advance Pay: Payment for any future cycle/advance in the current payroll run
• Absence Pay: Payment for leaves like sick leave, maternity leave, annual leave and other statutory leaves

• Define Batch Element Entry?
• This is an open interface specially designed for elements. With Batch Element Entry we can quickly incorporate mass updates in any specific elements.

Oracle Payroll Tables in Oracle Apps

PAY_ELEMENT_TYPES_F:-
This is a date track enabled table. This table stores the details about all the elements in the system. The Primary key is ELEMENT_TYPE_ID and the two date fields. This is usually used to get the name of the element, as ELEMENT_TYPE_ID is used in a lot of places to refer to the element.

PAY_ELEMENT_LINKS_F:-
This is also a date track enabled table. This table stores the details on the links. The primary key is: ELEMENT_LINK_ID and the two date tracked columns, stores the ELEMENT_TYPE_ID as the foreign key.

PAY_INPUT_VALUES_F:-
This is also Date track enabled table. This table stores the Input values for each element.  The primary key is INPUT_VALUE_ID and the two date tracked columns. This table also holds the ELEMENT_TYPE_ID as a foreign key to PAY_ELEMENT_TYPES_F. This can be used to pull in the Element input value name.

PAY_ELEMENT_ENTRIES_F:- This is also Date tracked table. This one stores the details about the element entries. The table stores the Entries with the ASSIGNMENT_ID and the ELEMENT_LINK_ID as foreign key. The Primary key is ELEMENT_ENTRY_ID. This table also links itself to PAY_ELEMENT_TYPES_F with storing ELEMENT_TYPE_ID as a foreign key.

PAY_ELEMENT_ENTRY_VALUES_F:- This is also Date tracked table. It stores the values for each entry. This table has only 6 columns. Out of which,  the Primary key is: ELEMENT_ENTRY_VALUE_ID and the two date tracked columns, it stores the ELEMENT_ENTRY_ID as the foreign key to PAY_ELEMENT_ENTRIES_F and the SCREEN_ENTRY_VALUE stores the actual value of the Input Value. The INPUT_VALUE_ID column links the table to the Input values table (PAY_INPUT_VALUES_F).

PAY_PAYROLL_ACTIONS:- This table logs all the actions taken by the Payroll Engine. Primary key is PAYROLL_ACTION_ID, and it logs in each and every activity. The Table is capable enough to store a lot of information as it has got columns to store all kind of data used in Payroll. It does not populate all the columns / row. However it logs in only the ones those are needed.

PAY_RUN_RESULTS:- This table stores the status related to the elements against the assignment actions. The primary key is RUN_RESULT_ID. ELEMENT_TYPE_ID and ASSIGNMENT_ACTION_ID are the two-other important foreign keys.

PAY_RUN_RESULT_VALUES:- This table takes the RUN_RESULT_ID and the INPUT_VALUE_ID and stores the Value obtained by the Payroll Engine.

Few More tables:-

Table Name	Date Tracked?	Primary Key	Description
PAY_ACCRUAL_PLANS	No	ACCRUAL_PLAN_ID	Stores details on the Accrual Plans. ACCRUAL_PLAN_ELEMENT_TYPE_ID can be used for Element value links.
			Total Compensation -> Basic -> Accrual Plans
PAY_PAYROLLS_F	Yes	PAYROLL_ID	Stores the Payroll related data.
			Payroll-> Description
PAY_ASSIGNMENT_ACTIONS	No	ASSIGNMENT_ACTION_ID	Stores the Payroll actions against Assignments with action status and sequence. Links with PAYROLL_ACTION_ID.
PAY_ASSIGNMENT_LATEST_BALANCES	No		Logs the Balances with DEFINED_BALANCE_ID with each and every assignment action, related to ASSIGNMENT_ACTION_ID.
PAY_ASSIGNMENT_LINK_USAGES_F	Yes	ASSIGNMENT_LINK_USAGE_ID	Logs the assignment and element links with date tracking capabilities. ELEMENT_LINK_ID and ASSIGNMENT_ID are the two foreign keys.
PAY_BALANCE_ATTRIBUTES	No	BALANCE_ATTRIBUTE_ID	Stores the Balance attributes.
			Total Compensation -> Basic -> Balance-> Attributes(B)
PAY_BALANCE_CATEGORIES_F	Yes	BALANCE_CATEGORY_ID	Stores the Balance Categories that can be used while defining Balances.
PAY_BALANCE_DIMENSIONS	No	BALANCE_DIMENSION_ID	Stores the Balance Dimensions.
PAY_BALANCE_FEEDS_F	Yes	BALANCE_FEED_ID	Stores the Balance Feeds.
PAY_BALANCE_TYPES	No	BALANCE_TYPE_ID	Stores the Balances.
PAY_BATCH_LINES	No	BATCH_LINE_ID	Logs the Batch lines at the Batch element entries. Linking the BATCH_ID, ASSIGNMENT_ID and ELEMENT_TYPE_ID. Logs the BATCH_LINE_STATUS.
PAY_COSTS	No	COST_ID	Logs the Cost and the balances as Debits or Credits with respect to each RUN_RESULT_ID and ASSIGNMENT_ACTION_ID.
PAY_COST_ALLOCATIONS_F	Yes	COST_ALLOCATION_ID	Logs the Cost against a COST_ALLOCATION_KEYFLEX_ID, with reference to assignment Ids. This is where costing is logged.
PAY_COST_ALLOCATION_KEYFLEX	No	COST_ALLOCATION_KEYFLEX_ID	The Cost allocation KFF table.
PAY_DEFINED_BALANCES	No	DEFINED_BALANCE_ID	Clubs the Balance with its Dimensions, using BALANCE_TYPE_ID and BALANCE_DIMENSION_ID.
PAY_ELEMENT_CLASSIFICATIONS	No	CLASSIFICATION_ID	Stores the Classifications.
			Total Compensation -> Basic -> Classification
PAY_ELEMENT_SETS	No	ELEMENT_SET_ID	Stores details on Element Sets.
			Payroll-> Element Set
PAY_ENTRY_PROCESS_DETAILS	No		Logs the details of Run results with RUN_RESULT_ID and ELEMENT_ENTRY_ID.
PAY_LINK_INPUT_VALUES_F	Yes	LINK_INPUT_VALUE_ID	Clubs Input Values with the Element links.
PAY_MESSAGE_LINES	No		Logs Payroll Messages
PAY_PAYMENT_TYPES	No	PAYMENT_TYPE_ID	Stores the Payment Methods.
			Payroll-> Payment Methods
PAY_PEOPLE_GROUPS	No	PEOPLE_GROUP_ID	The People Group KFF table.
PAY_PRE_PAYMENTS	No	PRE_PAYMENT_ID	Logs the Prepayment information against ASSIGNMENT_ACTION_ID with the Monetary Values.
PAY_QUICKPAY_INCLUSIONS	No		Logs quickpay actions against ASSIGNMENT_ACTION_ID and ELEMENT_ENTRY_ID
PAY_RETRO_DEFINITIONS	No	RETRO_DEFINITION_ID	Stores Retro Definitions.
PAY_RUN_BALANCES	No	RUN_BALANCE_ID	Stores the Balance Updates against ASSIGNMENT_ID and DEFINED_BALANCE_ID.
PAY_RUN_TYPES_F	Yes	RUN_TYPE_ID	Stores the different Payroll Run Types.