Oracle Apps HRMS Organization classification and Types

Organization classification and type information

SELECT HAOU.NAME,HOI.ORG_INFORMATION1,HOI.ORG_INFORMATION_CONTEXT,HOIT.DISPLAYED_ORG_INFORMATION_TYPE
FROM HR_ORGANIZATION_INFORMATION HOI, HR_ALL_ORGANIZATION_UNITS HAOU,HR_ORG_INFORMATION_TYPES HOIT
WHERE HOI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
-- AND HOI.ORG_INFORMATION1 = 'HR_BG'
AND HOIT.ORG_INFORMATION_TYPE = HOI.ORG_INFORMATION_CONTEXT

Oracle Apps HRMS Employees Current Salary

Current Salaries of the employees.


SELECT papf.employee_number,papf.full_name,pj.NAME job,pap.NAME as Designation ,pg.NAME as Grade , haou.NAME ORGANIZATION,ppp.creation_date createDate,ppp.PROPOSED_SALARY as salary
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_jobs pj
,per_grades pg
,hr_all_organization_units haou
,per_position_definitions ppd
,per_all_positions pap
,per_pay_proposals ppp
WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.current_employee_flag = 'Y'
AND papf.employee_number IS NOT NULL
AND paaf.person_id = papf.person_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.job_id = pj.job_id
AND paaf.organization_id = haou.organization_id
AND paaf.grade_id = pg.grade_id
AND paaf.position_id = pap.position_id
AND pap.position_definition_id = ppd.position_definition_id
AND ppp.pay_proposal_id in (SELECT MAX (pay_proposal_id)
FROM per_pay_proposals
WHERE assignment_id = paaf.assignment_id)
order by to_number(papf.employee_number)

Oracle Apps HRMS Employee Current Salary

Current Salaries of the employees.


SELECT papf.employee_number
,papf.full_name
,pj.NAME job
,haou.NAME ORGANIZATION
,ppp.proposed_salary_n salary
FROM per_all_people_f papf
,per_all_assignments_f paaf
,per_jobs pj
,hr_all_organization_units haou
,per_position_definitions ppd
,per_all_positions pap
,per_pay_proposals ppp
WHERE 1 = 1
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.current_employee_flag = 'Y'
AND papf.employee_number IS NOT NULL
AND paaf.person_id = papf.person_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.job_id = pj.job_id
AND paaf.organization_id = haou.organization_id
AND paaf.position_id = pap.position_id
AND pap.position_definition_id = ppd.position_definition_id
AND ppp.pay_proposal_id = (SELECT MAX (pay_proposal_id)
FROM per_pay_proposals
WHERE assignment_id = paaf.assignment_id)

Oracle Apps HRMS Employee Allowances

How to find out the allowances amount of an employee

SELECT paaf.PERSON_ID,papf.FULL_NAME,paaf.PAYROLL_ID,c.NAME as allowordeduc
FROM pay_element_entry_values_f f, PAY_INPUT_VALUES_F c,
PAY_ELEMENT_LINKS_F b,PAY_LINK_INPUT_VALUES_F d,PAY_ELEMENT_ENTRIES_F A,PER_ALL_ASSIGNMENTS_F paaf,per_all_people_f papf
WHERE C.INPUT_VALUE_ID(+) = f.INPUT_VALUE_ID
--and UPPER(c.NAME) LIKE UPPER( 'Amount')
and d.ELEMENT_LINK_ID=b.ELEMENT_LINK_ID
and papf.person_id = paaf.person_id
AND d.INPUT_VALUE_ID=c.INPUT_VALUE_ID
and a.element_entry_id = F.element_entry_id
AND a.effective_start_date = F.effective_start_date
AND A.EFFECTIVE_END_DATE >= TO_DATE (sysdate,'dd-mon-RRRR')
AND a.assignment_id = paaf.assignment_id
AND b.PAYROLL_ID = 112
AND paaf.ASSIGNMENT_ID =327
AND paaf.business_group_id = 626
and b.element_link_id = a.element_link_id
AND b.effective_end_date >=SYSDATE
AND paaf.effective_end_date >=SYSDATE;

Oracle Apps HRMS Organization Stucture

Find out the Hierarchy of the Organization Information


SELECT HAOU.NAME ,HAOU1.NAME,HAOU1.TYPE,HOI.ORG_INFORMATION_CONTEXT ,HAOU1.INTERNAL_ADDRESS_LINE,HAOU1.INTERNAL_EXTERNAL_FLAG,
HL.DESCRIPTION,HL.ADDRESS_LINE_1,HOIT.ORG_INFORMATION_TYPE,HOIT.DESCRIPTION,HOIT.DISPLAYED_ORG_INFORMATION_TYPE,HL.POSTAL_CODE,HL.COUNTRY
FROM HR_ALL_ORGANIZATION_UNITS HAOU,HR_ALL_ORGANIZATION_UNITS HAOU1,HR_LOCATIONS HL,HR_ORGANIZATION_INFORMATION HOI,
HR_ORG_INFORMATION_TYPES HOIT
WHERE HAOU.ORGANIZATION_ID = HAOU1.BUSINESS_GROUP_ID
AND HL.LOCATION_ID = HAOU1.LOCATION_ID
AND HAOU1.ORGANIZATION_ID = HOI.ORG_INFORMATION_ID
AND HOIT.ORG_INFORMATION_TYPE = HOI.ORG_INFORMATION_CONTEXT
AND HAOU1.INTERNAL_EXTERNAL_FLAG IN ('INT', 'EXT')
ORDER BY 1

Oracle APPS HRMS Query for Education Background

Given query will extract data for employee's education background.

SELECT PAPF.PERSON_ID,PAPF.FIRST_NAME ||' ' || PAPF.LAST_NAME AS NAME,PJ.NAME AS JOBS ,PG.NAME AS GRADE ,PAP.NAME AS POSITION,
HAOU.NAME AS ORAGNAIZATION,PPT.SYSTEM_PERSON_TYPE,PQT.NAME AS DEGR,PQ.TITLE,PQ.GRADE_ATTAINED,PQ.AWARDED_DATE,PQ.START_DATE,PQ.END_DATE
FROM PER_ALL_PEOPLE_F PAPF,HR_ALL_ORGANIZATION_UNITS HAOU,PER_ALL_ASSIGNMENTS_F PASF,PER_JOBS PJ,PER_GRADES PG,
PER_ALL_POSITIONS PAP,PER_PERSON_TYPES PPT,PER_QUALIFICATIONS PQ,PER_QUALIFICATION_TYPES PQT
WHERE PASF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PASF.LOCATION_ID = HAOU.LOCATION_ID
AND PASF.PERSON_ID = PAPF.PERSON_ID
AND PAPF.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP','APP')
AND PJ.JOB_ID = PASF.JOB_ID
AND PG.GRADE_ID = PASF.GRADE_ID
AND PAP.POSITION_ID = PASF.POSITION_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PASF.EFFECTIVE_END_DATE > sysdate
AND PQ.QUALIFICATION_TYPE_ID = PQT.QUALIFICATION_TYPE_ID
AND PQ.PERSON_ID = PAPF.PERSON_ID
ORDER BY 1,4

Oracle APPS Instance.

We need to registered on the given link http://vis1211.solutionbeacon.net/sbreguser.html. After registration, the user name and password will be emailed on your given address. There are complete ERP as actual for practice.

Oracle Apps Payroll Query for Elements.

Query show for the payment elements relationships

select pes.ELEMENT_SET_NAME,petf.ELEMENT_NAME,petf.DESCRIPTION
from HR.PAY_ELEMENT_SETS pes ,HR.PAY_ELEMENT_TYPE_RULES petr,HR.PAY_ELEMENT_TYPES_F petf
where pes.ELEMENT_SET_ID = petr.ELEMENT_SET_ID
and petf.ELEMENT_TYPE_ID = petr.ELEMENT_TYPE_ID

Oracle Apps Payroll Terminology

1. Elements.

Element is a unit used to build earnings, benefits and deductions that can be give to employees.

2. Input Values

Input value are like the place holder for holding actual values of elements. An element can have more then one input values.

3. Elements Links.

Play a role to link the elements to employees.This link can be done by associating an element to payroll and then associating that payroll to an employees assignment.
An element can also be linked to employees by Employment, Category, Grade and job.

4. Element Entry Value

Actual Value of the entry.

5. Payroll Action.

Any payroll related actions performed on employee's assignment is referred to as Payroll Action.

6. Assignment Action.

This is record of actions performed by the payroll process on each and every assignment.

7. Run Resul

This is the actual calculated result of the payroll process.

Oracle Apps Types of Tables Suffix

There Four type of Suffix in oracle apps table Structures.
1. _TL Used to store language specific information (Translation Tables)
2. _V These are the views.
3. _VL These are the views based on Translation Tables.
4. _ALL Indicate Multi Organization Tables.

Note. If there is no suffix, it means normal table.

Difference Between KFF and DFF in Oracle Apps

KFF - Key flex field

1. Which builds unique entity identifiers.
2. It is used to capture the company key information.
3. KFF's data are stored in the segments.
4. KFF's 30 segment columns are reserved for KFF's(but it is not mandatory It is based on the requirement).

DFF - Descriptive flex field

1. To sore the additional information.
2. It is used to capture extra info from end user without changing form code and without altering database table.
3. DFF's data are stored in the Attributes.
4. In apps 15 attribute columns are reserved for the DFF's data.

Key Flex Field in GL Oracle Apps

There Is only one key flexfield in gl that is Key flexifield table structure column Accounting flex field Gl_code_combinations's Charts_of_accounts_id.

Key Flexfields in Oracle HRMS.

There are 10 KFF in Oracle HRMS.

1. Job
2. Position
3. Grade
4. People Group
5. Cost Allocation
6. Competence
7. Bank Details
8. Soft Coded
9. Personal Analysis criteria
10. Collective Agreement.

Registering New Forms in Oracle Apps

A quick quid to create and deploy a form on Oracle Apps.

Creating and deployement.

1. Findout the template form template.fmb(It stored under the appltst\au\versoin_number\forms\US).
2. Save as you want to save form name.
3. Create block canvas and logic what you required on form.
4. Compile it and save it into to other directory where executable are located(appltst\gl\version_number\forms\US)

Configuration with Oracle Apps

1. Connect to Oracle Apps for Registration of create and deployed form.
2. From the responsibilities select the Application Developer.
3. Application Developer > Application > Form
4. In the Form field enter your form name without extension.
and select the application where you want to attach it like GL, HRMS, AR etc then save it.
5. Create a function your forms Application Developer > Application > Function
6. Enter descriptive function name ,user function name save it.
7. Then select the form tab for linking the Form with functions. in the form field select LOV and and select you newly created form name and save it and close form.
8. The third step is create the menu Application Developer > Application > Menu
9. Give menu name and user menu name then enter value in seq number and select you previously created function and save it.
10. Now it is a time the create responsibility for menu attachments.
11. Give responsibility name , select application where you want to attach it and then give the Data group and request group information and finely select menu name, create in previous steps.
12. Assign this responsibility the to the user, connect this user and test your good work.
13. Select Security > User and select the user and add responsibility to the user.

Oracle APPS HRMS Queries

Oracle Apps HRMS Absence.


SELECT PAPF.PERSON_ID,PAPF.FIRST_NAME ||' ' || PAPF.LAST_NAME AS NAME,PJ.NAME AS JOBS ,PG.NAME AS GRADE ,PAP.NAME AS POSITION,HAOU.NAME AS ORAGNAIZATION, PAAT.NAME AS LEAVE,PAA.DATE_START,PAA.DATE_END,PAA.ABSENCE_DAYS,PAA.TIME_START,PAA.TIME_END,ABSENCE_HOURS
FROM PER_ABSENCE_ATTENDANCES PAA,PER_ABSENCE_ATTENDANCE_TYPES PAAT
,PER_ALL_PEOPLE_F PAPF,HR_ALL_ORGANIZATION_UNITS HAOU,PER_ALL_ASSIGNMENTS_F PASF,PER_JOBS PJ,PER_GRADES PG,
PER_ALL_POSITIONS PAP
WHERE PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAPF.PERSON_ID = PAA.PERSON_ID
AND PASF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PASF.LOCATION_ID = HAOU.LOCATION_ID
AND PASF.PERSON_ID = PAPF.PERSON_ID
AND PJ.JOB_ID = PASF.JOB_ID
AND PG.GRADE_ID = PASF.GRADE_ID
AND PAP.POSITION_ID = PASF.POSITION_ID
AND PAPF.EFFECTIVE_END_DATE > SYSDATE
AND PASF.EFFECTIVE_END_DATE > sysdate
ORDER BY 1,4

Oracle APPS HRMS Table Structur

Personnel Information of employees


SELECT PA.PERSON_ID,PRO.TITLE||' '||PRO.FIRST_NAME||' '||LAST_NAME, PJ.NAME AS JOB ,OU.NAME AS ORGANIZATION,
PA.ADDRESS_LINE1,ADDRESS_TYPE,PA.POSTAL_CODE,PA.TELEPHONE_NUMBER_1,PA.TOWN_OR_CITY
FROM PER_ADDRESSES PA, per_all_people_f PRO,HR_ALL_ORGANIZATION_UNITS OU,PER_ALL_ASSIGNMENTS_F PAS,PER_JOBS PJ,hr_locations LC
WHERE PA.PERSON_ID = PRO.PERSON_ID
AND PAS.ORGANIZATION_ID = OU.ORGANIZATION_ID
AND PAS.LOCATION_ID = LC.LOCATION_ID
AND PRO.PERSON_ID = PAS.PERSON_ID
AND PRO.BUSINESS_GROUP_ID = 202
AND PJ.JOB_ID = PAS.JOB_ID
AND PRO.PERSON_ID = '55'
AND PRO.effective_end_date > sysdate
AND PAS.effective_end_date > sysdate;

Oracle DB Backup Utility

The give batch script will create the automatic oracle database backup. Just create the batch file and sechdule it.



echo off
set baseDir=c:\backup\
set vDirName=%date:~0,2%%date:~3,2%%date:~6,4%
set vDumpFileName=%vDirName%-%time:~0,2%-%time:~3,2%-%time:~6,2%

set backupDir=%baseDir%%vDirName%
mkdir %backupDir%

echo direcotr create Date:%baseDir%%vDirName%
echo %vDumpFileName%

expdp scott/scott schemas=SCOTT directory=TEST_DIR dumpfile=%vDumpFileName%.DMP logfile=%vDumpFileName%.log

move c:\backup\%vDumpFileName%.log c:\backup\%vDirName%
move c:\backup\%vDumpFileName%.DMP c:\backup\%vDirName%
echo successfuly moved.