VIEW: APPS.HRFV_SALARY_PRO

Source

View Text - Preformatted

SELECT bgrT.name business_group_name , peo.full_name person_name , peo.employee_number employee_number , asg.assignment_number assignment_number , pro.proposed_salary_n salary_amount , ppb.pay_annualization_factor annualization_factor , ppb.pay_annualization_factor * pro.proposed_salary_n annual_salary , pet.input_currency_code currency , pro.change_date effective_from_date , pro_next.change_date-1 effective_to_date , pro.last_change_date salary_last_change_date , hr_bis.bis_decode_lookup('YES_NO',pro.approved) approved , asg.normal_hours working_hours_amount , asg.frequency working_hours_frequency , pay.payroll_name payroll , tpt.number_per_fiscal_year number_per_fiscal_year , hr_bis.bis_decode_lookup('PAY_BASIS',ppb.pay_basis) pay_basis , pro.forced_ranking ranking , DECODE(pro.multiple_components ,NULL,NULL ,hr_bis.bis_decode_lookup('YES_NO',pro.multiple_components)) multiple_components , ppb.name salary_bases , '_DF:PER:PER_PAY_PROPOSALS:pro' , nvl(pro_next.change_date-1,hr_general.end_of_time) effective_to_date_nn , pet.input_currency_code currency_code , pet.output_currency_code output_currency_code , pro.approved approved_code , ppb.pay_basis pay_basis_code , pro.multiple_components multiple_components_code , asg.assignment_id assignment_id , asg.person_id person_id , asg.business_group_id business_group_id , asg.organization_id organization_id , asg.location_id location_id , asg.job_id job_id , asg.grade_id grade_id , asg.position_id position_id , pay.payroll_id payroll_id , pro.pay_proposal_id pay_proposal_id , pro.performance_review_id performance_review_id , pro.event_id event_id , ppb.pay_basis_id pay_basis_id , ppb.input_value_id input_value_id , ppb.rate_id rate_id , pet.element_type_id element_type_id FROM per_assignments_f asg , per_all_people_f peo , per_pay_bases ppb , per_pay_proposals pro , per_pay_proposals pro_next , pay_input_values_f piv , pay_element_types_f pet , pay_payrolls_f pay , per_time_period_types tpt , hr_all_organization_units_tl bgrT WHERE peo.current_employee_flag = 'Y' AND pro.assignment_id = asg.assignment_id AND pro.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.person_id = peo.person_id AND pro.change_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND asg.pay_basis_id = ppb.pay_basis_id AND pro.assignment_id = pro_next.assignment_id (+) AND pro.change_date = pro_next.last_change_date (+) AND ppb.input_value_id = piv.input_value_id AND piv.element_type_id = pet.element_type_id AND pro.change_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND asg.payroll_id = pay.payroll_id AND pro.change_date BETWEEN pay.effective_start_date AND pay.effective_end_date AND pay.period_type = tpt.period_type AND asg.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PRO.PROPOSED_SALARY_N SALARY_AMOUNT
, PPB.PAY_ANNUALIZATION_FACTOR ANNUALIZATION_FACTOR
, PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N ANNUAL_SALARY
, PET.INPUT_CURRENCY_CODE CURRENCY
, PRO.CHANGE_DATE EFFECTIVE_FROM_DATE
, PRO_NEXT.CHANGE_DATE-1 EFFECTIVE_TO_DATE
, PRO.LAST_CHANGE_DATE SALARY_LAST_CHANGE_DATE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.APPROVED) APPROVED
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, ASG.FREQUENCY WORKING_HOURS_FREQUENCY
, PAY.PAYROLL_NAME PAYROLL
, TPT.NUMBER_PER_FISCAL_YEAR NUMBER_PER_FISCAL_YEAR
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS) PAY_BASIS
, PRO.FORCED_RANKING RANKING
, DECODE(PRO.MULTIPLE_COMPONENTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.MULTIPLE_COMPONENTS)) MULTIPLE_COMPONENTS
, PPB.NAME SALARY_BASES
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, NVL(PRO_NEXT.CHANGE_DATE-1
, HR_GENERAL.END_OF_TIME) EFFECTIVE_TO_DATE_NN
, PET.INPUT_CURRENCY_CODE CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, PRO.APPROVED APPROVED_CODE
, PPB.PAY_BASIS PAY_BASIS_CODE
, PRO.MULTIPLE_COMPONENTS MULTIPLE_COMPONENTS_CODE
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.PERSON_ID PERSON_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.LOCATION_ID LOCATION_ID
, ASG.JOB_ID JOB_ID
, ASG.GRADE_ID GRADE_ID
, ASG.POSITION_ID POSITION_ID
, PAY.PAYROLL_ID PAYROLL_ID
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, PRO.EVENT_ID EVENT_ID
, PPB.PAY_BASIS_ID PAY_BASIS_ID
, PPB.INPUT_VALUE_ID INPUT_VALUE_ID
, PPB.RATE_ID RATE_ID
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
FROM PER_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, PER_PAY_BASES PPB
, PER_PAY_PROPOSALS PRO
, PER_PAY_PROPOSALS PRO_NEXT
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PAY_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
WHERE PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PRO.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.PERSON_ID = PEO.PERSON_ID
AND PRO.CHANGE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PRO.ASSIGNMENT_ID = PRO_NEXT.ASSIGNMENT_ID (+)
AND PRO.CHANGE_DATE = PRO_NEXT.LAST_CHANGE_DATE (+)
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRO.CHANGE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID
AND PRO.CHANGE_DATE BETWEEN PAY.EFFECTIVE_START_DATE
AND PAY.EFFECTIVE_END_DATE
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY