VIEW: APPS.HRFV_SALARIES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,asg.assignment_number assignment_number ,ppb.pay_annualization_factor * pro.proposed_salary_n annual_salary ,peo.employee_number employee_number ,pro.change_date salary_change_date ,pro.proposed_salary_n salary_amount ,TO_NUMBER(null) absolute_increment ,TO_NUMBER(null) percentage_increment ,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',DECODE(pee.element_entry_id,null,'N','Y')) approved_flag ,NVL(evt.date_start,rev.review_date) performance_review_date ,DECODE(rev.performance_rating,NULL,NULL, hr_bis.bis_decode_lookup('PERFORMANCE_RATING',rev.performance_rating)) performance_rating ,asg.normal_hours working_hours_amount ,asg.frequency working_hours_frequency ,TO_NUMBER(inf.org_information3) organization_working_hours ,pos.working_hours position_working_hours ,inf.org_information4 org_working_hours_frequency ,pos.frequency pos_working_hours_frequency ,tpt.number_per_fiscal_year number_per_fiscal_year ,hr_bis.bis_decode_lookup('PAY_BASIS',ppb.pay_basis) pay_basis ,TO_CHAR(null) prev_pay_basis ,ppb.rate_basis grade_rate_basis ,pro.forced_ranking ranking ,pet.input_currency_code currency_code ,hr_bis.bis_decode_lookup('UOM',piv.uom) salary_amount_unit_of_measure ,per_views_pkg.per_calc_comparatio( asg.assignment_id ,pro.change_date ,pro.proposed_salary_n ,pee.element_entry_id ,asg.normal_hours ,TO_NUMBER(inf.org_information3) ,TO_NUMBER(pos.working_hours) ,inf.org_information4 ,pos.frequency ,tpt.number_per_fiscal_year ,asg.grade_id ,ppb.rate_id ,ppb.pay_basis ,ppb.rate_basis ,asg.business_group_id) grade_comparatio ,ser.date_start hire_date ,ROUND(MONTHS_BETWEEN(pee.effective_start_date,ser.date_start)/12) years_service ,DECODE(pro.multiple_components,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',pro.multiple_components)) multiple_components ,orgT.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,locT.location_code location_name ,pro.next_sal_review_date next_salary_review_date ,ppb.name salary_bases ,hr_bis.bis_decode_lookup('YES_NO', DECODE(peo.current_employee_flag,null,'N','Y')) current_employee_flag ,pet.output_currency_code output_currency_code ,'_DF:PER:PER_PAY_PROPOSALS:pro' ,'_DF:PAY:PAY_ELEMENT_ENTRIES:pee' ,asg.assignment_id assignment_id ,asg.business_group_id business_group_id ,pee.element_entry_id element_entry_id ,asg.organization_id organization_id ,asg.job_id job_id ,asg.position_id position_id ,asg.grade_id grade_id ,asg.location_id location_id ,pro.pay_proposal_id pay_proposal_id ,pro.performance_review_id performance_review_id ,peo.person_id person_id ,ppb.rate_id rate_id FROM per_events evt ,pay_all_payrolls_f pay ,per_time_period_types tpt ,hr_all_positions_f_tl pft ,per_all_positions pos ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,per_performance_reviews rev ,per_jobs_tl jbt ,per_grades_tl gdt ,hr_locations_all_tl locT ,hr_organization_information inf ,pay_element_types_f pet ,pay_input_values_f piv ,per_pay_bases ppb ,per_assignments_f asg ,per_people_f peo ,per_person_types ppt ,pay_element_entries_f pee ,per_pay_proposals pro ,per_periods_of_service ser WHERE pro.event_id = evt.event_id(+) AND pay.period_type = tpt.period_type(+) AND asg.payroll_id = pay.payroll_id(+) AND asg.person_id = peo.person_id AND peo.person_type_id = ppt.person_type_id AND ppt.system_person_type IN ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') AND asg.effective_start_date BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+) AND asg.position_id = pos.position_id(+) AND pft.position_id (+) = pos.position_id AND pft.language (+) = userenv('LANG') AND asg.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND asg.job_id = jbt.job_id(+) AND jbt.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') AND pro.performance_review_id = rev.performance_review_id (+) AND asg.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND asg.organization_id = inf.organization_id(+) AND inf.org_information_context (+) = 'Work Day Information' AND piv.element_type_id = pet.element_type_id(+) AND piv.effective_start_date BETWEEN pet.effective_start_date(+) AND pet.effective_end_date(+) AND piv.input_value_id(+) = ppb.input_value_id AND (pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date OR ppb.pay_basis_id is null) AND asg.pay_basis_id = ppb.pay_basis_id(+) AND (pro.change_date IS NULL OR NOT EXISTS (SELECT 1 FROM pay_element_entry_values_f pev2 ,pay_element_entries_f pee2 ,per_pay_bases ppb2 ,per_assignments_f asg2 WHERE asg2.assignment_id = pro.assignment_id AND asg2.pay_basis_id = ppb2.pay_basis_id AND pee2.assignment_id = asg2.assignment_id AND pee2.entry_type = 'E' AND pev2.element_entry_id = pee2.element_entry_id AND pev2.input_value_id = ppb2.input_value_id AND pev2.effective_start_date = pee2.effective_start_date AND pro.change_date - 1 BETWEEN asg2.effective_start_date AND asg2.effective_end_date AND pro.change_date - 1 BETWEEN pee2.effective_start_date AND pee2.effective_end_date ) ) AND pee.creator_type(+) = 'SP' AND pee.creator_id(+) = pro.pay_proposal_id AND pee.effective_start_date(+) = pro.change_date AND pee.assignment_id(+) = pro.assignment_id AND pro.assignment_id = asg.assignment_id AND NVL(pro.change_date,NVL(pro.review_date, evt.date_start)) BETWEEN asg.effective_start_date AND asg.effective_end_date AND NVL(pro.change_date,NVL(pro.review_date, evt.date_start)) BETWEEN peo.effective_start_date AND peo.effective_end_date AND ser.period_of_service_id = asg.period_of_service_id AND asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) UNION ALL SELECT bgrT.name business_group_name ,peo.full_name person_name ,asg.assignment_number assignment_number ,ppb.pay_annualization_factor * pro.proposed_salary_n annual_salary ,peo.employee_number employee_number ,pro.change_date salary_change_date ,pro.proposed_salary_n salary_amount ,round(TO_NUMBER(DECODE(ppb.pay_basis,ppb2.pay_basis, TO_CHAR(pro.proposed_salary_n - TO_NUMBER(pev2.screen_entry_value)), TO_CHAR(((pro.proposed_salary_n*ppb.pay_annualization_factor) - (TO_NUMBER(pev2.screen_entry_value)*ppb2.pay_annualization_factor))/ (ppb.pay_annualization_factor)))),2) absolute_increment ,TO_NUMBER(DECODE(ppb.pay_basis,ppb2.pay_basis, TO_CHAR(ROUND(((pro.proposed_salary_n - TO_NUMBER(pev2.screen_entry_value))/ DECODE(TO_NUMBER(pev2.screen_entry_value), 0, null , TO_NUMBER(pev2.screen_entry_value))) * 100 ,2)) , TO_CHAR(ROUND((((pro.proposed_salary_n*ppb.pay_annualization_factor) - (TO_NUMBER(pev2.screen_entry_value)*ppb2.pay_annualization_factor)) / DECODE(TO_NUMBER(pev2.screen_entry_value), 0, null, (TO_NUMBER(pev2.screen_entry_value)*ppb2.pay_annualization_factor))) * 100 ,2)))) percentage_increment ,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',DECODE(pee.element_entry_id,null,'N','Y')) approved_flag ,NVL(evt.date_start,pro.review_date) performance_review_date ,DECODE(rev.performance_rating,NULL,NULL, hr_bis.bis_decode_lookup('PERFORMANCE_RATING',rev.performance_rating)) performance_rating ,asg.normal_hours working_hours_amount ,asg.frequency working_hours_frequency ,TO_NUMBER(inf.org_information3) organization_working_hours ,pos.working_hours position_working_hours ,inf.org_information4 org_working_hours_frequency ,pos.frequency pos_working_hours_frequency ,tpt.number_per_fiscal_year number_per_fiscal_year ,hr_bis.bis_decode_lookup('PAY_BASIS',ppb.pay_basis) pay_basis ,hr_bis.bis_decode_lookup('PAY_BASIS',ppb2.pay_basis) prev_pay_basis ,ppb.rate_basis grade_rate_basis ,pro.forced_ranking ranking ,pet.input_currency_code currency_code ,hr_bis.bis_decode_lookup('UOM',piv.uom) salary_amount_unit_of_measure ,per_views_pkg.per_calc_comparatio (asg.assignment_id ,pro.change_date ,pro.proposed_salary_n ,pee.element_entry_id ,asg.normal_hours ,TO_NUMBER(inf.org_information3) ,TO_NUMBER(pos.working_hours) ,inf.org_information4 ,pos.frequency ,tpt.number_per_fiscal_year ,asg.grade_id ,ppb.rate_id ,ppb.pay_basis ,ppb.rate_basis ,asg.business_group_id) grade_comparatio ,ser.date_start hire_date ,ROUND(MONTHS_BETWEEN(pee.effective_start_date,ser.date_start)/12) years_service ,DECODE(pro.multiple_components,NULL,NULL, hr_bis.bis_decode_lookup('YES_NO',pro.multiple_components)) multiple_components ,orgT.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,locT.location_code location_name ,pro.next_sal_review_date next_salary_review_date ,ppb.name salary_bases ,hr_bis.bis_decode_lookup('YES_NO', DECODE(peo.current_employee_flag,null,'N','Y')) current_employee_flag ,pet.output_currency_code output_currency_code ,'_DF:PER:PER_PAY_PROPOSALS:pro' ,'_DF:PAY:PAY_ELEMENT_ENTRIES:pee' ,asg.assignment_id assignment_id ,asg.business_group_id business_group_id ,pee.element_entry_id element_entry_id ,asg.organization_id organization_id ,asg.job_id job_id ,asg.position_id position_id ,asg.grade_id grade_id ,asg.location_id location_id ,pro.pay_proposal_id pay_proposal_id ,pro.performance_review_id performance_review_id ,peo.person_id person_id ,ppb.rate_id rate_id FROM per_events evt ,pay_all_payrolls_f pay ,per_time_period_types tpt ,hr_all_positions_f_tl pft ,per_all_positions pos ,hr_all_organization_units_tl orgT ,hr_all_organization_units_tl bgrT ,per_performance_reviews rev ,per_jobs_tl jbt ,per_grades_tl gdt ,hr_locations_all_tl locT ,hr_organization_information inf ,pay_element_types_f pet ,pay_input_values_f piv ,per_pay_bases ppb ,per_pay_bases ppb2 ,pay_element_entry_values_f pev2 ,pay_element_entries_f pee2 ,pay_element_entries_f pee ,per_assignments_f asg2 ,per_assignments_f asg ,per_people_f peo ,per_person_types ppt ,per_pay_proposals pro ,per_periods_of_service ser WHERE pro.event_id = evt.event_id(+) AND pay.period_type = tpt.period_type(+) AND asg.payroll_id = pay.payroll_id(+) AND asg.person_id = peo.person_id AND peo.person_type_id = ppt.person_type_id AND ppt.system_person_type IN ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') AND asg.effective_start_date BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+) AND asg.position_id = pos.position_id(+) AND pft.position_id (+) = pos.position_id AND pft.language (+) = userenv('LANG') AND asg.organization_id = orgT.organization_id AND orgT.language = userenv('LANG') AND asg.job_id = jbt.job_id(+) AND jbt.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') AND pro.performance_review_id = rev.performance_review_id (+) AND asg.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') AND asg.organization_id = inf.organization_id(+) AND inf.org_information_context (+) = 'Work Day Information' AND piv.element_type_id = pet.element_type_id AND piv.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND ppb.input_value_id = piv.input_value_id AND pro.change_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND asg.pay_basis_id = ppb.pay_basis_id AND pev2.input_value_id = ppb2.input_value_id AND pev2.element_entry_id = pee2.element_entry_id AND pev2.effective_start_date = pee2.effective_start_date AND asg2.pay_basis_id = ppb2.pay_basis_id AND asg2.assignment_id = pro.assignment_id AND pro.change_date - 1 BETWEEN asg2.effective_start_date AND asg2.effective_end_date AND pee2.assignment_id = pro.assignment_id AND pee2.entry_type = 'E' AND pro.change_date - 1 BETWEEN pee2.effective_start_date AND pee2.effective_end_date AND pee.creator_type(+) = 'SP' AND pee.creator_id(+) = pro.pay_proposal_id AND pee.effective_start_date(+) = pro.change_date AND pee.assignment_id(+) = pro.assignment_id AND pro.assignment_id = asg.assignment_id AND pro.change_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND pro.change_date BETWEEN peo.effective_start_date AND peo.effective_end_date AND ser.period_of_service_id = asg.period_of_service_id 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
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N ANNUAL_SALARY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PRO.CHANGE_DATE SALARY_CHANGE_DATE
, PRO.PROPOSED_SALARY_N SALARY_AMOUNT
, TO_NUMBER(NULL) ABSOLUTE_INCREMENT
, TO_NUMBER(NULL) PERCENTAGE_INCREMENT
, 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'
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')) APPROVED_FLAG
, NVL(EVT.DATE_START
, REV.REVIEW_DATE) PERFORMANCE_REVIEW_DATE
, DECODE(REV.PERFORMANCE_RATING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PERFORMANCE_RATING'
, REV.PERFORMANCE_RATING)) PERFORMANCE_RATING
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, ASG.FREQUENCY WORKING_HOURS_FREQUENCY
, TO_NUMBER(INF.ORG_INFORMATION3) ORGANIZATION_WORKING_HOURS
, POS.WORKING_HOURS POSITION_WORKING_HOURS
, INF.ORG_INFORMATION4 ORG_WORKING_HOURS_FREQUENCY
, POS.FREQUENCY POS_WORKING_HOURS_FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR NUMBER_PER_FISCAL_YEAR
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS) PAY_BASIS
, TO_CHAR(NULL) PREV_PAY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, PRO.FORCED_RANKING RANKING
, PET.INPUT_CURRENCY_CODE CURRENCY_CODE
, HR_BIS.BIS_DECODE_LOOKUP('UOM'
, PIV.UOM) SALARY_AMOUNT_UNIT_OF_MEASURE
, PER_VIEWS_PKG.PER_CALC_COMPARATIO( ASG.ASSIGNMENT_ID
, PRO.CHANGE_DATE
, PRO.PROPOSED_SALARY_N
, PEE.ELEMENT_ENTRY_ID
, ASG.NORMAL_HOURS
, TO_NUMBER(INF.ORG_INFORMATION3)
, TO_NUMBER(POS.WORKING_HOURS)
, INF.ORG_INFORMATION4
, POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, PPB.RATE_BASIS
, ASG.BUSINESS_GROUP_ID) GRADE_COMPARATIO
, SER.DATE_START HIRE_DATE
, ROUND(MONTHS_BETWEEN(PEE.EFFECTIVE_START_DATE
, SER.DATE_START)/12) YEARS_SERVICE
, DECODE(PRO.MULTIPLE_COMPONENTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.MULTIPLE_COMPONENTS)) MULTIPLE_COMPONENTS
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PRO.NEXT_SAL_REVIEW_DATE NEXT_SALARY_REVIEW_DATE
, PPB.NAME SALARY_BASES
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, 'N'
, 'Y')) CURRENT_EMPLOYEE_FLAG
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, '_DF:PAY:PAY_ELEMENT_ENTRIES:PEE'
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, PEO.PERSON_ID PERSON_ID
, PPB.RATE_ID RATE_ID
FROM PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, HR_ALL_POSITIONS_F_TL PFT
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERFORMANCE_REVIEWS REV
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCT
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, PER_PERSON_TYPES PPT
, PAY_ELEMENT_ENTRIES_F PEE
, PER_PAY_PROPOSALS PRO
, PER_PERIODS_OF_SERVICE SER
WHERE PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.PERSON_ID = PEO.PERSON_ID
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL'
, 'EX_EMP'
, 'EX_EMP_APL')
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+)
AND PAY.EFFECTIVE_END_DATE(+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND PFT.POSITION_ID (+) = POS.POSITION_ID
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASG.JOB_ID = JBT.JOB_ID(+)
AND JBT.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')
AND PRO.PERFORMANCE_REVIEW_ID = REV.PERFORMANCE_REVIEW_ID (+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ORGANIZATION_ID = INF.ORGANIZATION_ID(+)
AND INF.ORG_INFORMATION_CONTEXT (+) = 'WORK DAY INFORMATION'
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID(+)
AND PIV.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE(+)
AND PET.EFFECTIVE_END_DATE(+)
AND PIV.INPUT_VALUE_ID(+) = PPB.INPUT_VALUE_ID
AND (PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE OR PPB.PAY_BASIS_ID IS NULL)
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID(+)
AND (PRO.CHANGE_DATE IS NULL OR NOT EXISTS (SELECT 1
FROM PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRIES_F PEE2
, PER_PAY_BASES PPB2
, PER_ASSIGNMENTS_F ASG2
WHERE ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND ASG2.PAY_BASIS_ID = PPB2.PAY_BASIS_ID
AND PEE2.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND PEE2.ENTRY_TYPE = 'E'
AND PEV2.ELEMENT_ENTRY_ID = PEE2.ELEMENT_ENTRY_ID
AND PEV2.INPUT_VALUE_ID = PPB2.INPUT_VALUE_ID
AND PEV2.EFFECTIVE_START_DATE = PEE2.EFFECTIVE_START_DATE
AND PRO.CHANGE_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE - 1 BETWEEN PEE2.EFFECTIVE_START_DATE
AND PEE2.EFFECTIVE_END_DATE ) )
AND PEE.CREATOR_TYPE(+) = 'SP'
AND PEE.CREATOR_ID(+) = PRO.PAY_PROPOSAL_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PEE.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND NVL(PRO.CHANGE_DATE
, NVL(PRO.REVIEW_DATE
, EVT.DATE_START)) BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND NVL(PRO.CHANGE_DATE
, NVL(PRO.REVIEW_DATE
, EVT.DATE_START)) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SER.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) UNION ALL SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N ANNUAL_SALARY
, PEO.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PRO.CHANGE_DATE SALARY_CHANGE_DATE
, PRO.PROPOSED_SALARY_N SALARY_AMOUNT
, ROUND(TO_NUMBER(DECODE(PPB.PAY_BASIS
, PPB2.PAY_BASIS
, TO_CHAR(PRO.PROPOSED_SALARY_N - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))
, TO_CHAR(((PRO.PROPOSED_SALARY_N*PPB.PAY_ANNUALIZATION_FACTOR) - (TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)*PPB2.PAY_ANNUALIZATION_FACTOR))/ (PPB.PAY_ANNUALIZATION_FACTOR))))
, 2) ABSOLUTE_INCREMENT
, TO_NUMBER(DECODE(PPB.PAY_BASIS
, PPB2.PAY_BASIS
, TO_CHAR(ROUND(((PRO.PROPOSED_SALARY_N - TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))/ DECODE(TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)
, 0
, NULL
, TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE))) * 100
, 2))
, TO_CHAR(ROUND((((PRO.PROPOSED_SALARY_N*PPB.PAY_ANNUALIZATION_FACTOR) - (TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)*PPB2.PAY_ANNUALIZATION_FACTOR)) / DECODE(TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)
, 0
, NULL
, (TO_NUMBER(PEV2.SCREEN_ENTRY_VALUE)*PPB2.PAY_ANNUALIZATION_FACTOR))) * 100
, 2)))) PERCENTAGE_INCREMENT
, 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'
, DECODE(PEE.ELEMENT_ENTRY_ID
, NULL
, 'N'
, 'Y')) APPROVED_FLAG
, NVL(EVT.DATE_START
, PRO.REVIEW_DATE) PERFORMANCE_REVIEW_DATE
, DECODE(REV.PERFORMANCE_RATING
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('PERFORMANCE_RATING'
, REV.PERFORMANCE_RATING)) PERFORMANCE_RATING
, ASG.NORMAL_HOURS WORKING_HOURS_AMOUNT
, ASG.FREQUENCY WORKING_HOURS_FREQUENCY
, TO_NUMBER(INF.ORG_INFORMATION3) ORGANIZATION_WORKING_HOURS
, POS.WORKING_HOURS POSITION_WORKING_HOURS
, INF.ORG_INFORMATION4 ORG_WORKING_HOURS_FREQUENCY
, POS.FREQUENCY POS_WORKING_HOURS_FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR NUMBER_PER_FISCAL_YEAR
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB.PAY_BASIS) PAY_BASIS
, HR_BIS.BIS_DECODE_LOOKUP('PAY_BASIS'
, PPB2.PAY_BASIS) PREV_PAY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, PRO.FORCED_RANKING RANKING
, PET.INPUT_CURRENCY_CODE CURRENCY_CODE
, HR_BIS.BIS_DECODE_LOOKUP('UOM'
, PIV.UOM) SALARY_AMOUNT_UNIT_OF_MEASURE
, PER_VIEWS_PKG.PER_CALC_COMPARATIO (ASG.ASSIGNMENT_ID
, PRO.CHANGE_DATE
, PRO.PROPOSED_SALARY_N
, PEE.ELEMENT_ENTRY_ID
, ASG.NORMAL_HOURS
, TO_NUMBER(INF.ORG_INFORMATION3)
, TO_NUMBER(POS.WORKING_HOURS)
, INF.ORG_INFORMATION4
, POS.FREQUENCY
, TPT.NUMBER_PER_FISCAL_YEAR
, ASG.GRADE_ID
, PPB.RATE_ID
, PPB.PAY_BASIS
, PPB.RATE_BASIS
, ASG.BUSINESS_GROUP_ID) GRADE_COMPARATIO
, SER.DATE_START HIRE_DATE
, ROUND(MONTHS_BETWEEN(PEE.EFFECTIVE_START_DATE
, SER.DATE_START)/12) YEARS_SERVICE
, DECODE(PRO.MULTIPLE_COMPONENTS
, NULL
, NULL
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PRO.MULTIPLE_COMPONENTS)) MULTIPLE_COMPONENTS
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, PRO.NEXT_SAL_REVIEW_DATE NEXT_SALARY_REVIEW_DATE
, PPB.NAME SALARY_BASES
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(PEO.CURRENT_EMPLOYEE_FLAG
, NULL
, 'N'
, 'Y')) CURRENT_EMPLOYEE_FLAG
, PET.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, '_DF:PER:PER_PAY_PROPOSALS:PRO'
, '_DF:PAY:PAY_ELEMENT_ENTRIES:PEE'
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PEE.ELEMENT_ENTRY_ID ELEMENT_ENTRY_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, PRO.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, PEO.PERSON_ID PERSON_ID
, PPB.RATE_ID RATE_ID
FROM PER_EVENTS EVT
, PAY_ALL_PAYROLLS_F PAY
, PER_TIME_PERIOD_TYPES TPT
, HR_ALL_POSITIONS_F_TL PFT
, PER_ALL_POSITIONS POS
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_PERFORMANCE_REVIEWS REV
, PER_JOBS_TL JBT
, PER_GRADES_TL GDT
, HR_LOCATIONS_ALL_TL LOCT
, HR_ORGANIZATION_INFORMATION INF
, PAY_ELEMENT_TYPES_F PET
, PAY_INPUT_VALUES_F PIV
, PER_PAY_BASES PPB
, PER_PAY_BASES PPB2
, PAY_ELEMENT_ENTRY_VALUES_F PEV2
, PAY_ELEMENT_ENTRIES_F PEE2
, PAY_ELEMENT_ENTRIES_F PEE
, PER_ASSIGNMENTS_F ASG2
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, PER_PERSON_TYPES PPT
, PER_PAY_PROPOSALS PRO
, PER_PERIODS_OF_SERVICE SER
WHERE PRO.EVENT_ID = EVT.EVENT_ID(+)
AND PAY.PERIOD_TYPE = TPT.PERIOD_TYPE(+)
AND ASG.PAYROLL_ID = PAY.PAYROLL_ID(+)
AND ASG.PERSON_ID = PEO.PERSON_ID
AND PEO.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE IN ('EMP'
, 'EMP_APL'
, 'EX_EMP'
, 'EX_EMP_APL')
AND ASG.EFFECTIVE_START_DATE BETWEEN PAY.EFFECTIVE_START_DATE(+)
AND PAY.EFFECTIVE_END_DATE(+)
AND ASG.POSITION_ID = POS.POSITION_ID(+)
AND PFT.POSITION_ID (+) = POS.POSITION_ID
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ORGT.LANGUAGE = USERENV('LANG')
AND ASG.JOB_ID = JBT.JOB_ID(+)
AND JBT.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')
AND PRO.PERFORMANCE_REVIEW_ID = REV.PERFORMANCE_REVIEW_ID (+)
AND ASG.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ASG.ORGANIZATION_ID = INF.ORGANIZATION_ID(+)
AND INF.ORG_INFORMATION_CONTEXT (+) = 'WORK DAY INFORMATION'
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PIV.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRO.CHANGE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PEV2.INPUT_VALUE_ID = PPB2.INPUT_VALUE_ID
AND PEV2.ELEMENT_ENTRY_ID = PEE2.ELEMENT_ENTRY_ID
AND PEV2.EFFECTIVE_START_DATE = PEE2.EFFECTIVE_START_DATE
AND ASG2.PAY_BASIS_ID = PPB2.PAY_BASIS_ID
AND ASG2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE - 1 BETWEEN ASG2.EFFECTIVE_START_DATE
AND ASG2.EFFECTIVE_END_DATE
AND PEE2.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PEE2.ENTRY_TYPE = 'E'
AND PRO.CHANGE_DATE - 1 BETWEEN PEE2.EFFECTIVE_START_DATE
AND PEE2.EFFECTIVE_END_DATE
AND PEE.CREATOR_TYPE(+) = 'SP'
AND PEE.CREATOR_ID(+) = PRO.PAY_PROPOSAL_ID
AND PEE.EFFECTIVE_START_DATE(+) = PRO.CHANGE_DATE
AND PEE.ASSIGNMENT_ID(+) = PRO.ASSIGNMENT_ID
AND PRO.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND PRO.CHANGE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PRO.CHANGE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND SER.PERIOD_OF_SERVICE_ID = ASG.PERIOD_OF_SERVICE_ID
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY