VIEW: APPS.HRFV_CURRENT_SALARIES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,ppb.pay_annualization_factor * fnd_number.canonical_to_number(pev.screen_entry_value) annual_salary ,peo.employee_number employee_number ,orgT.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,locT.location_code location_name ,ppb.name salary_basis ,ppb.pay_annualization_factor pay_annualization_factor ,pev.effective_start_date change_date ,ROUND(MONTHS_BETWEEN(pee.effective_start_date, pes.date_start)/12) years_service ,elt.input_currency_code currency_code ,asg.assignment_id assignment_id ,peo.business_group_id business_group_id ,gdt.grade_id grade_id ,jbt.job_id job_id ,orgT.organization_id organization_id ,peo.person_id person_id ,pft.position_id position_id ,locT.location_id location_id FROM pay_element_links_x ell ,pay_element_types_x elt ,per_people_x peo ,hr_all_organization_units_tl orgT ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,per_grades_tl gdt ,hr_locations_all_tl locT ,pay_input_values_x piv ,per_periods_of_service pes ,pay_element_entry_values_x pev ,per_pay_bases ppb ,pay_element_entries_x pee ,hr_all_organization_units_tl bgrT ,hr_all_organization_units bgr ,per_assignments_x asg ,per_assignment_status_types ast WHERE bgr.organization_id = bgr.business_group_id AND bgr.organization_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND asg.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND piv.input_value_id = ppb.input_value_id AND asg.assignment_id = pee.assignment_id AND asg.assignment_type = 'E' AND asg.period_of_service_id = pes.period_of_service_id AND asg.business_group_id = bgr.business_group_id AND pee.element_entry_id = pev.element_entry_id AND pev.input_value_id = piv.input_value_id AND pee.element_link_id = ell.element_link_id AND ell.element_type_id = elt.element_type_id AND asg.pay_basis_id = ppb.pay_basis_id AND ppb.input_value_id = piv.input_value_id AND elt.element_type_id = piv.element_type_id AND asg.person_id = peo.person_id AND asg.assignment_status_type_id = ast.assignment_status_type_id AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') AND peo.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, peo.business_group_id) AND asg.job_id = jbt.job_id (+) AND jbt.language (+) = userenv('LANG') AND asg.position_id = pft.position_id (+) AND pft.language (+) = userenv('LANG') AND asg.grade_id = gdt.grade_id (+) AND gdt.language (+) = userenv('LANG') AND asg.location_id = locT.location_id (+) AND locT.language (+) = userenv('LANG') WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PPB.PAY_ANNUALIZATION_FACTOR * FND_NUMBER.CANONICAL_TO_NUMBER(PEV.SCREEN_ENTRY_VALUE) ANNUAL_SALARY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PPB.NAME SALARY_BASIS
, PPB.PAY_ANNUALIZATION_FACTOR PAY_ANNUALIZATION_FACTOR
, PEV.EFFECTIVE_START_DATE CHANGE_DATE
, ROUND(MONTHS_BETWEEN(PEE.EFFECTIVE_START_DATE
, PES.DATE_START)/12) YEARS_SERVICE
, ELT.INPUT_CURRENCY_CODE CURRENCY_CODE
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, PEO.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GDT.GRADE_ID GRADE_ID
, JBT.JOB_ID JOB_ID
, ORGT.ORGANIZATION_ID ORGANIZATION_ID
, PEO.PERSON_ID PERSON_ID
, PFT.POSITION_ID POSITION_ID
, LOCT.LOCATION_ID LOCATION_ID
FROM PAY_ELEMENT_LINKS_X ELL
, PAY_ELEMENT_TYPES_X ELT
, PER_PEOPLE_X PEO
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCT
, PAY_INPUT_VALUES_X PIV
, PER_PERIODS_OF_SERVICE PES
, PAY_ELEMENT_ENTRY_VALUES_X PEV
, PER_PAY_BASES PPB
, PAY_ELEMENT_ENTRIES_X PEE
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_ASSIGNMENTS_X ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
WHERE BGR.ORGANIZATION_ID = BGR.BUSINESS_GROUP_ID
AND BGR.ORGANIZATION_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND PIV.INPUT_VALUE_ID = PPB.INPUT_VALUE_ID
AND ASG.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ASG.PERIOD_OF_SERVICE_ID = PES.PERIOD_OF_SERVICE_ID
AND ASG.BUSINESS_GROUP_ID = BGR.BUSINESS_GROUP_ID
AND PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID
AND PEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PEE.ELEMENT_LINK_ID = ELL.ELEMENT_LINK_ID
AND ELL.ELEMENT_TYPE_ID = ELT.ELEMENT_TYPE_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND ELT.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND ASG.PERSON_ID = PEO.PERSON_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN'
, 'SUSP_ASSIGN')
AND PEO.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEO.BUSINESS_GROUP_ID)
AND ASG.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND ASG.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASG.GRADE_ID = GDT.GRADE_ID (+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND LOCT.LANGUAGE (+) = USERENV('LANG') WITH READ ONLY