Registering New Forms in Oracle Apps

Follow the instructions in give link

http://www.exforsys.com/tutorials/oracle-apps/registering-new-forms-in-oracle-apps-11i.html

Oracle Apps HRMS Payroll's Important Joins

Important joins in Payroll.

select *
from pay_consolidation_sets pcs,pay_all_payrolls_f papf,pay_payroll_actions ppa
,pay_element_sets pes
,hr_assignment_sets has
where pcs.CONSOLIDATION_SET_ID = papf.CONSOLIDATION_SET_ID
and ppa.CONSOLIDATION_SET_ID = pcs.CONSOLIDATION_SET_ID
and pes.ELEMENT_SET_ID = ppa.ELEMENT_SET_ID
and has.ASSIGNMENT_SET_ID = ppa.ASSIGNMENT_SET_ID

Oracle Apps HRMS Employee Salary History

Employee's salary History with reasons.

SELECT pro.ROWID, pro.pay_proposal_id, pro.business_group_id,
pro.assignment_id, pro.performance_review_id, prv.review_date,
hl2.meaning, pro.proposed_salary, TO_NUMBER (pro.proposed_salary),
pro.change_date, pro.proposal_reason, hl1.meaning,
pro.last_change_date, pro.next_sal_review_date, pro.forced_ranking,
pro.attribute_category, pro.attribute1, pro.attribute2,
pro.multiple_components,
pro.approved, pro.comments, pro.object_version_number,
pro.last_update_date, pro.last_updated_by, pro.last_update_login,
pro.created_by, pro.creation_date
FROM hr_lookups hl1,
hr_lookups hl2,
per_performance_reviews prv,
per_pay_proposals pro
WHERE pro.performance_review_id = prv.performance_review_id(+)
AND hl2.lookup_code(+) = prv.performance_rating
AND hl2.lookup_type(+) = 'PERFORMANCE_RATING'
AND hl1.lookup_code(+) = pro.proposal_reason
AND hl1.lookup_type(+) = 'PROPOSAL_REASON'

Oracle Apps HRMS lookups details

Oracle HRMS lookups details.

SELECT LOOKUP_CODE,MEANING,LOOKUP_TYPE
FROM FND_COMMON_LOOKUPS
WHERE APPLICATION_ID = 800

Oracle Apps HRMS Deductions and Earnings.

How to find out the payment elements deductions and Earnings ?

SELECT DISTINCT PET.ELEMENT_NAME,PET.ELEMENT_TYPE_ID,PET.EFFECTIVE_START_DATE,PET.REPORTING_NAME,PEC.CLASSIFICATION_NAME
FROM PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_TYPES_F PET
WHERE PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PEC.CLASSIFICATION_NAME IN ('Earnings','Tax Deductions')
ORDER BY PEC.CLASSIFICATION_NAME

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)