Oracle HR / Payroll Important TablesThe tables that are date-tracked will have two columns:-Effective_start_dateEffective_end_dateAll the objects in Oracle HRMS or Payroll that end with _x have a where clause where sysdate between Effective_start_date AND Effective_end_dateHence these are views that return records as of SYSDATE.The primary keys of the date tracked columns includes Effective_start_date & Effective_end_date.

PAY_ELEMENT_TYPES_F – Payroll ElementsFirstly, we create some elements, which get created in table PAY_ELEMENT_TYPES_F. The primary key is a combination of element_type_Id along with Date Track columns.When will you join to pay_element_types_f ?1. To display the name of Element in Reports2. When payroll runs, the results are stored in PAY_RUN_RESULTS, which stores a reference to element_type_Id.

PAY_ELEMENT_LINKS_F – Payroll Element LinksTo make payroll elements eligible to a group of people, you create Element Links.The Primary key is ELEMENT_LINK_ID with date-track columns.When will you commonly use element_link_Id ?1. When querying on Element Entry[PAY_ELEMENT_ENTRIES_F], a join can be made using ELEMENT_LINK_ID2. The reason Oracle uses ELEMENT_LINK_ID in Element Entry to work out Costing Segments based on Payroll Costing Hierarchy.

PER_ALL_PEOPLE_F – Employee recordIt is well known that Employee records are stored in PER_ALL_PEOPLE_F. Its a date track table with primary key being person_Id. This table also has party_Id, because Oracle creates a party in TCA as soon as a record in per_all_people_f gets created.Main usage of per_all_people_f:-1. To get the name of the person

2. To get the date of birth or tax Id of the personNote:- The application uses PER_PEOPLE_F, as that is a secured view layer on top of PER_ALL_PEOPLE_F

PER_ALL_ASSIGNMENTS_F – Assignment table:-This is the most central table in Oracle Payroll. Payroll engine uses this table as the main driver.Why so: Because Element Entries are stored against Assignment record.This table is date-tracked, with primary key being assignment_IdUsage of per_all_assignments_f?1. Find position_Id, hence position, or grade, the organization for the persons assignment.2. It has foreign key to person_id. Each person Id can have no more than one primary assignment at any given point  in time.3. Pay run results and also the pay_assignment actions refers to this table.

PER_PERSON_TYPES – Person typeThis is the master table for Person Types. Some examples of Person Types are Employees, Casuals, Applicants etc.The primary key is person_type_id.But please do not try joining this with person_type_id in per_all_people_f.Instead join that to per_person_type_usages_f_x will give you person_type usage as of SYSDATE.For any other date, use the classic p_date between effective_start_date and effective_end_date.

PAY_ELEMENT_ENTRIES_F & PAY_ELEMENT_ENTRY_VALUES_F – Tables effected when element entry is doneThese two tables are inserted into when fresh Element Entries are created.PAY_ELEMENT_ENTRIES_FEach Element that gets attached to an Assignment will have an entry in PAY_ELEMENT_ENTRIES_F.For each assignment you will have one or more records in PAY_ELEMENT_ENTRIES_F table.It is logical that PAY_ELEMENT_ENTRIES_F has following columnsAssignment_idElement_link_idELEMENT_TYPE_IDThis table is date-tracked too. Please do not ask my where there was a need to store both ELEMENT_TYPE_ID and also

ELEMENT_LINK_ID in this table.Just storing the ELEMENT_LINK_ID could suffice. However, i guess Oracle did so for Performance reasons.

PAY_ELEMENT_ENTRY_VALUES_FThis table stores a reference to PAY_ELEMENT_ENTRIES_F. In plain English, this table captures the entry value for the elements.The Input Value is stored in SCREEN_ENTRY_VALUE. The name suggests that it stores the Formatted Screen value. However, I can assure you that SCREEN_ENTRY_VALUE stores the non formatted value. For example screen might showHH:MM as 03:30, but SCREEN_ENTRY_VALUE will have 3.5This table is date-tracked, and its primary key is INPUT_VALUE_ID.Where can I commonly join INPUT_VALUE_ID to ?To the payroll run results value table, i.e. PAY_RUN_RESULT_VALUESYou can also join to PAY_COSTS, if you wish to work out which input value contributed to a specific Payroll Costed Amount.

Fnd_Session, Oracle HR Date TrackingIn this article I am going to explain Fnd_Session and cover the answers of following questions.

1)  How Date Track Works?

2) Per_people_v return null rows or select * from per_people_v return null rows?

3)  In Oracle Report Builder how to use FND_SESSIONS. How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE?

4) What is date tracking in Oracle HRMS?

5) Why there are multiple records in per_people_f against each employee.

6) What is fnd_session

Q: How Date Track Works ?

Q: In Oracle Report Builder how to use FND_SESSIONS. How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE?

Q: What is date tracking in Oracle HRMS?

When you try to update or delete a record in People screen or some other screens in HR Module. A Window appear with Two Options Update or Corrections.

Update: When you press Update button it create new row in database with new effecting date and value and old records remains there as history.

Correction: When you press Correction button it override the existing value.

In case if you update a record on 01-Jan-10 (effective date also 01-Jan-10) and you update record with effective date 15-dec-09 you will  further prompted for the type of update, as follows:

Update Insert:  By presseing intert button oracle will insert another record in database with effect from 15-Dec-09 till 01-Jan-10 and it will also change any previous record’s End effective date with 14-Dec-09.

Update Override (Replace):   Update effect from current effect date to end date of the last record.

Q: Per_people_v return null rows or select * from per_people_v return null rows

To query per_people_v you have to insert a session_ID and effect_date in fnd_session table. If you study the HRMS views it has a join with fnd_session’s effect date.  When you query a view it looks into the Fnd_Session’s effect date, either its between current record’s effect date or not. So before querying Oracle HRMS views like per_people_v use following insert command and you will start getting results based upon your current effective date.

Insert into Fnd_Sessions (Session_id,effective_date)   (select userenv(‘sessionid’),sysdate from dual) ;

Now if you query  select * from per_people_v , you will start getting results.

 Q: In Oracle Report Builder how to use FND_SESSIONS. 

How do i get FND_SESSIONS value. I am even setting APPS_INITIALISE

 In Oracle Report Builder If you are using per_people_v in your query, you will get null rows to prevent this problem you can use “After Parameter Form” trigger.

and write following code.  

insert into fnd_sessions    (session_id, effective_date)    values (userenv(‘sessionid’),:p_effective_date);

Q: What is fnd_sessions

FND_SESSIONS is table of APPLSYS schema which. when you  logon to a screen which is based on Date sensitive information, Oracle will prompt you with two options1. Change the Effective Date of the current logged on SESSION2. Retain the SYSDATE as Current Effective date

If you click on YES, then you will get an opportunity to change the Current Session Date

OK, the Date Selected by user for date-track is stored in fnd_sessions with their sessionid.  Now onward whenever you will update a record oracle will pic effective_date  from fnd_sessions against you sessionid. You can insert sessionid and effective_date  into fnd_session from SQL Plus or Toad as well and get required results.

Query to find out payroll Costing Detail  /  ”How to find out payroll costed accounts” / “Query to find out Payroll Charged Accounts”.

    SELECT distinct d.EMPLOYEE_NUMBER,d.FULL_NAME,a.CONCATENATED_SEGMENTS, a.element_name,decode(a.debit_or_credit,’Debit’,COSTED_VALUE,”) Debit  ,    decode(debit_or_credit,’Credit’,COSTED_VALUE,”) Credit            FROM  pay_costs_v a, pay_assignment_actions_v b,per_assignments_x c,per_people_x d    where     d.EMPLOYEE_NUMBER= nvl(:p_emp_no,d.EMPLOYEE_NUMBER)    and   trunc (b.EFFECTIVE_DATE)  between :p_from_date and :p_to_date    and a.ASSIGNMENT_ACTION_ID=b.ASSIGNMENT_ACTION_ID    and  b.ASSIGNMENT_ID=c.ASSIGNMENT_ID    and c.PERSON_ID=d.PERSON_ID

Query to Find Out Elements added Manually. 

     select a.EMPLOYEE_NUMBER,a.FULL_NAME,c.ELEMENT_NAME,c.CREATED_BY,C.ELEMENT_ENTRY_ID,C.ELEMENT_TYPE_ID,e.name,d.SCREEN_ENTRY_VALUE     from per_people_x a,per_assignments_x b,PAY_PAYWSMEE_ELEMENT_ENTRIES c,PAY_ELEMENT_ENTRY_VALUES_F d,pay_input_values_f_tl e     where a.EMPLOYEE_NUMBER=’530579′     and c.EFFECTIVE_start_DATE>=’01-mar-10′     and c.effective_end_date<=’31-mar-10′     and a.PERSON_ID=b.PERSON_ID     and b.ASSIGNMENT_ID=c.ASSIGNMENT_ID     and c.CREATED_BY <>-1     –and c.ELEMENT_NAME=’Previous Month Overtime Hours’–     and c.PROCESSED=’N’     and c.ELEMENT_ENTRY_ID=d.ELEMENT_ENTRY_ID

     and d.INPUT_VALUE_ID=e.INPUT_VALUE_ID     and e.LANGUAGE=’US’     and d.SCREEN_ENTRY_VALUE is not null

 

Query fo Find out all Earning and Deduction Elements and values after Payroll Run

SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE,TP.PERIOD_NAME,paf.ORGANIZATION_ID,sum(decode(pec.CLASSIFICATION_NAME,’Earnings’,to_number(rrv.result_value),                       0)   ) Earnings,   sum(decode(pec.CLASSIFICATION_NAME,’Voluntary Deductions’,to_number(rrv.result_value),            ‘Involuntary Deductions’,to_number(rrv.result_value),              ‘Employer Charges’,to_number(rrv.result_value),                    0)   ) Deductions  –ety.element_name,ety.CLASSIFICATION_ID– PD.SEGMENT5  POSITION_NO,PD.SEGMENT6 POSITION_NAME,     FROM per_people_x ppf,per_assignments_x paf,pay_assignment_actions pas,pay_payroll_actions ppa,pay_run_results rr,pay_run_result_values rrv,pay_element_types_f ety,pay_input_values_F I,PER_TIME_PERIODS TP,PAY_ELEMENT_CLASSIFICATIONS_VL pec

WHERE ppf.person_id = paf.person_id    AND paf.assignment_id = pas.assignment_id    AND pas.assignment_action_id = rr.assignment_action_id    AND ppa.payroll_action_id = pas.payroll_action_id    AND rr.element_type_id = ety.element_type_id    AND i.element_type_id = ety.element_type_id    AND rrv.run_result_id = rr.run_result_id    AND rrv.input_value_id = i.input_value_id    and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID    and ety.CLASSIFICATION_ID=pec.CLASSIFICATION_ID    AND i.name = ‘Pay Value’ –   AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID   and ppa.EFFECTIVE_DATE  BETWEEN    :p_st_effect_date  AND  :p_end_effect_date   and ppf.employee_number  = nvl(:p_emp_number,ppf.employee_number)   –in(34000/*1546014859,14666,35343,15201,15202*/);group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,–To_Number(Wassa_HR_PACKAGE.Nid_Salary_By_Date(paf.assignment_id,ppa.EFFECTIVE_DATE,’N')),    ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,    TP.period_name,paf.ORGANIZATION_ID

Query for Oracle Payroll Salary Slip /  Salary Slip Detail Report Query / How to find out detail of salary slip/ Element wise Landscape Salary Slip at Payroll Run Levle  or quick pay level

Note:  I have hardcoded element names. You can also hardcode your elements to get Landscaped salary slip.

 SELECT ppf.employee_number,ppf.person_id,ppf.full_name,ppa.TIME_PERIOD_ID,ppa.EFFECTIVE_DATE  ,TP.PERIOD_NAME,       sum(decode(ety.element_name,’Basic Salary’                         ,TO_NUMBER(rrv.result_value),                                   ‘Basic Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),                                   ‘Basic Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),                                   0)) Earned_salary,

/********************************************************************************************************/                                         sum(decode(ety.element_name,’Transportation Allowance’                      ,TO_NUMBER(rrv.result_value),                                   ‘Transportation Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),                                   ‘Transportation Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),                                   0)) Transportation_allowance,/*************************************************************************************************************/                                         sum(decode(ety.element_name,’Work Type Allowance’                      ,TO_NUMBER(rrv.result_value),                                   ‘Work Type Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),                                   ‘Work Type Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),                                   0)) worktype_allowance,/***************************************************************************************************************/       sum(decode(ety.element_name,’Damages Allowance’                      ,TO_NUMBER(rrv.result_value),                                   ‘Damages Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),                                   ‘Damages Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),                                   0)) Damage_allowance,/*****************************************************************************************************************/       sum(decode(ety.element_name,’Danger Allowance’                      ,TO_NUMBER(rrv.result_value),                                   ‘Danger Sick Leave Payment’             ,TO_NUMBER(rrv.result_value),                                   ‘Danger Out Work Incident Leave Payment’,TO_NUMBER(rrv.result_value),                                   0)) Danger_allowance,   /*************************************************************************************************************/      sum(decode(ety.element_name,’Inflation Allowance’    ,TO_NUMBER(rrv.result_value),0)) Inflation_Allowance,/*********************************************************  Deductions  ************************************************/                                                sum(decode(ety.element_name,’Loan Recovery’         ,TO_NUMBER(rrv.result_value),0)) Loan_recovery,

       sum(decode(ety.element_name,’Loan Recovery Housing’ ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Housing,       sum(decode(ety.element_name,’Loan Recovery Others’  ,TO_NUMBER(rrv.result_value),0)) Loan_Recovery_Others,       sum(decode(ety.element_name,’Housing Deduction’     ,TO_NUMBER(rrv.result_value),0)) Housing_Deduction,       sum(decode(ety.element_name,’Penalty’               ,TO_NUMBER(rrv.result_value),0)) Penalty,/***********************************************************************************************************************/             sum(decode(ety.element_name,’Civil Pension’                      ,TO_NUMBER(rrv.result_value),                                   ‘Social Insurance’             ,TO_NUMBER(rrv.result_value),                                    0)) Civil_pension,/************************************************************************************************************************************/       sum(decode(ety.element_name,’In Out Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) In_Out_Leave_Deduction,       sum(decode(ety.element_name,’Unpaid Leave Deduction’ ,TO_NUMBER(rrv.result_value),0)) Unpaid_Leave_Deduction,       sum(decode(ety.element_name,’Retrieve Mony Recovery’ ,TO_NUMBER(rrv.result_value),0)) Retrieve_Mony_Recovery    FROM per_people_x ppf,    per_assignments_x paf,    pay_assignment_actions pas ,    pay_payroll_actions ppa,    pay_run_results rr,    pay_run_result_values rrv,    pay_element_types_f ety,    pay_input_values_F I ,    PER_TIME_PERIODS TP–     PER_POSITION_DEFINITIONS PD    — PAY_INPUT_VALUES_F      WHERE ppf.person_id = paf.person_id    AND paf.assignment_id = pas.assignment_id    AND pas.assignment_action_id = rr.assignment_action_id    AND ppa.payroll_action_id = pas.payroll_action_id    AND rr.element_type_id = ety.element_type_id    AND i.element_type_id = ety.element_type_id

    AND rrv.run_result_id = rr.run_result_id    AND rrv.input_value_id = i.input_value_id    and  TP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID    AND i.name = ‘Pay Value’ –   AND HR_GENERAL.DECODE_LATEST_POSITION_DEF_ID(PAF.POSITION_ID) = PD.POSITION_DEFINITION_ID   and ppa.EFFECTIVE_DATE  BETWEEN    :P_FROM_DATE      AND  :P_TO_DATE      and ppf.employee_number    = :P_Employee_number    –in(34000/*1546014859,14666,35343,15201,15202*/)    group by  ppf.full_name,ppa.TIME_PERIOD_ID,effective_date,    ppf.employee_number,ppf.person_id ,–PD.SEGMENT5 ,PD.SEGMENT6,    TP.period_nameorder  by  ppa.EFFECTIVE_DATE