VIEW: APPS.HRIFV_SAL_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.output_currency_code currency , pro.change_date effective_from_date , NVL(pro_next.change_date-1, NVL(pps.actual_termination_date,hr_general.end_of_time)) effective_to_date , pro.last_change_date salary_last_change_date ,DECODE(pro.proposal_reason,NULL,NULL, hr_bis.bis_decode_lookup('PROPOSAL_REASON',pro.proposal_reason)) salary_change_reason , hr_bis.bis_decode_lookup('YES_NO',pro.approved) approved , ppb.grade_annualization_factor grade_annualization_factor , 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_basis , '_DF:PER:PER_PAY_PROPOSALS:pro' , nvl(pro_next.change_date-1,hr_general.end_of_time) effective_to_date_nn , pet.output_currency_code 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 , asg.last_update_date asg_last_update_date , peo.last_update_date peo_last_update_date , pro.last_update_date pro_last_update_date , pro_next.last_update_date pro_next_last_update_date , piv.last_update_date piv_last_update_date , pet.last_update_date pet_last_update_date , pay.last_update_date pay_last_update_date , tpt.last_update_date tpt_last_update_date , asg.last_updated_by asg_last_updated_by , pro.last_updated_by pro_last_updated_by FROM per_assignments_f asg /* Secure */ , 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 ,per_periods_of_service pps WHERE peo.current_employee_flag = 'Y' AND pro.approved = 'Y' AND pro.assignment_id = asg.assignment_id AND pps.person_id = peo.person_id AND pro.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.period_of_service_id = pps.period_of_service_id 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 pro.approved = pro_next.approved (+) AND ppb.input_value_id = piv.input_value_id AND pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date 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.OUTPUT_CURRENCY_CODE CURRENCY
, PRO.CHANGE_DATE EFFECTIVE_FROM_DATE
, NVL(PRO_NEXT.CHANGE_DATE-1
, NVL(PPS.ACTUAL_TERMINATION_DATE
, HR_GENERAL.END_OF_TIME)) EFFECTIVE_TO_DATE
, PRO.LAST_CHANGE_DATE SALARY_LAST_CHANGE_DATE
, DECODE(PRO.PROPOSAL_REASON
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PROPOSAL_REASON'
, PRO.PROPOSAL_REASON)) SALARY_CHANGE_REASON
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.APPROVED) APPROVED
, PPB.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, 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_BASIS
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, NVL(PRO_NEXT.CHANGE_DATE-1
, HR_GENERAL.END_OF_TIME) EFFECTIVE_TO_DATE_NN
, PET.OUTPUT_CURRENCY_CODE 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
, ASG.LAST_UPDATE_DATE ASG_LAST_UPDATE_DATE
, PEO.LAST_UPDATE_DATE PEO_LAST_UPDATE_DATE
, PRO.LAST_UPDATE_DATE PRO_LAST_UPDATE_DATE
, PRO_NEXT.LAST_UPDATE_DATE PRO_NEXT_LAST_UPDATE_DATE
, PIV.LAST_UPDATE_DATE PIV_LAST_UPDATE_DATE
, PET.LAST_UPDATE_DATE PET_LAST_UPDATE_DATE
, PAY.LAST_UPDATE_DATE PAY_LAST_UPDATE_DATE
, TPT.LAST_UPDATE_DATE TPT_LAST_UPDATE_DATE
, ASG.LAST_UPDATED_BY ASG_LAST_UPDATED_BY
, PRO.LAST_UPDATED_BY PRO_LAST_UPDATED_BY
FROM PER_ASSIGNMENTS_F ASG /* SECURE */
, 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
, PER_PERIODS_OF_SERVICE PPS
WHERE PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PRO.APPROVED = 'Y'
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PPS.PERSON_ID = PEO.PERSON_ID
AND PRO.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
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 PRO.APPROVED = PRO_NEXT.APPROVED (+)
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
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