VIEW: APPS.HRI_MB_SALARY_CHANGE_V

Source

View Text - Preformatted

SELECT pro.proposed_salary * ppb.pay_annualization_factor annualized_salary , pro.prev_proposed_salary * ppb.pay_annualization_factor prev_annualized_salary , pro.proposed_salary_change * ppb.pay_annualization_factor annualized_salary_change , DECODE(pro.proposed_salary ,0,NULL ,(pro.proposed_salary_change) /pro.proposed_salary ) salary_change_prcnt , pro.proposed_salary salary , pro.proposed_salary_change salary_change , ppb.pay_annualization_factor annualization_factor , pro.prev_event_date - pro.event_date days_from_last_change , pro.event_date event_date , pro.prev_event_date prev_event_date , asg.assignment_id assignment_id , asg.person_id person_id , asg.business_group_id business_group_id , asg.organization_id organization_id , NVL(asg.location_id,-1) location_id , NVL(asg.job_id,-1) job_id , NVL(asg.grade_id,-1) grade_id , NVL(asg.position_id,-1) position_id , NVL(asg.supervisor_id,-1) supervisor_person_id , NVL(pro.pay_proposal_id,-1) pay_proposal_id , asg.assignment_type assignment_type_code , NVL(pet.input_currency_code ,'NA_EDW') currency_code , NVL(pet.output_currency_code ,'NA_EDW') output_currency_code , NVL(prv.Performance_Rating ,'NA_EDW') perf_rating_code , pro.multiple_components multiple_cmpnt_flag_code , NVL(pro.proposal_reason,'NA_EDW') salary_change_reason_code , pro.last_salary_flag last_salary_flag_code , GREATEST(asg.last_update_date ,pro.last_update_date) last_change_date , asg.last_update_date asg_last_update_date , pro.last_update_date pro_last_update_date FROM (SELECT pro_this.proposed_salary_n proposed_salary , pro_prev.proposed_salary_n prev_proposed_salary , pro_this.proposed_salary_n - NVL(pro_prev.proposed_salary_n,0) proposed_salary_change , pro_this.change_date event_date , pro_prev.change_date prev_event_date , pro_this.assignment_id assignment_id , pro_this.pay_proposal_id pay_proposal_id , pro_prev.pay_proposal_id prev_pay_proposal_id , pro_this.proposal_reason proposal_reason , pro_prev.proposal_reason prev_proposal_reason , pro_this.multiple_components multiple_components , pro_this.performance_review_id performance_review_id , DECODE(pro_prev.change_date ,NULL,'Y' ,'N') last_salary_flag , pro_this.last_update_date last_update_date FROM per_pay_proposals pro_this , per_pay_proposals pro_prev WHERE pro_this.approved = 'Y' AND pro_prev.approved (+) = 'Y' AND pro_this.assignment_id = pro_prev.assignment_id (+) AND pro_this.change_date = pro_prev.last_change_date (+) ) pro , per_all_assignments_f asg , per_pay_bases ppb , pay_input_values_f piv , pay_element_types_f pet , per_performance_reviews prv WHERE asg.assignment_id = pro.assignment_id AND asg.pay_basis_id = ppb.pay_basis_id AND pro.performance_review_id = prv.performance_review_id (+) AND ppb.input_value_id = piv.input_value_id AND piv.element_type_id = pet.element_type_id AND pro.event_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND pro.event_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pro.event_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND pro.event_date BETWEEN asg.effective_start_date AND asg.effective_end_date WITH READ ONLY

View Text - HTML Formatted

SELECT PRO.PROPOSED_SALARY * PPB.PAY_ANNUALIZATION_FACTOR ANNUALIZED_SALARY
, PRO.PREV_PROPOSED_SALARY * PPB.PAY_ANNUALIZATION_FACTOR PREV_ANNUALIZED_SALARY
, PRO.PROPOSED_SALARY_CHANGE * PPB.PAY_ANNUALIZATION_FACTOR ANNUALIZED_SALARY_CHANGE
, DECODE(PRO.PROPOSED_SALARY
, 0
, NULL
, (PRO.PROPOSED_SALARY_CHANGE) /PRO.PROPOSED_SALARY ) SALARY_CHANGE_PRCNT
, PRO.PROPOSED_SALARY SALARY
, PRO.PROPOSED_SALARY_CHANGE SALARY_CHANGE
, PPB.PAY_ANNUALIZATION_FACTOR ANNUALIZATION_FACTOR
, PRO.PREV_EVENT_DATE - PRO.EVENT_DATE DAYS_FROM_LAST_CHANGE
, PRO.EVENT_DATE EVENT_DATE
, PRO.PREV_EVENT_DATE PREV_EVENT_DATE
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.PERSON_ID PERSON_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, NVL(ASG.LOCATION_ID
, -1) LOCATION_ID
, NVL(ASG.JOB_ID
, -1) JOB_ID
, NVL(ASG.GRADE_ID
, -1) GRADE_ID
, NVL(ASG.POSITION_ID
, -1) POSITION_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, NVL(PRO.PAY_PROPOSAL_ID
, -1) PAY_PROPOSAL_ID
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, NVL(PET.INPUT_CURRENCY_CODE
, 'NA_EDW') CURRENCY_CODE
, NVL(PET.OUTPUT_CURRENCY_CODE
, 'NA_EDW') OUTPUT_CURRENCY_CODE
, NVL(PRV.PERFORMANCE_RATING
, 'NA_EDW') PERF_RATING_CODE
, PRO.MULTIPLE_COMPONENTS MULTIPLE_CMPNT_FLAG_CODE
, NVL(PRO.PROPOSAL_REASON
, 'NA_EDW') SALARY_CHANGE_REASON_CODE
, PRO.LAST_SALARY_FLAG LAST_SALARY_FLAG_CODE
, GREATEST(ASG.LAST_UPDATE_DATE
, PRO.LAST_UPDATE_DATE) LAST_CHANGE_DATE
, ASG.LAST_UPDATE_DATE ASG_LAST_UPDATE_DATE
, PRO.LAST_UPDATE_DATE PRO_LAST_UPDATE_DATE
FROM (SELECT PRO_THIS.PROPOSED_SALARY_N PROPOSED_SALARY
, PRO_PREV.PROPOSED_SALARY_N PREV_PROPOSED_SALARY
, PRO_THIS.PROPOSED_SALARY_N - NVL(PRO_PREV.PROPOSED_SALARY_N
, 0) PROPOSED_SALARY_CHANGE
, PRO_THIS.CHANGE_DATE EVENT_DATE
, PRO_PREV.CHANGE_DATE PREV_EVENT_DATE
, PRO_THIS.ASSIGNMENT_ID ASSIGNMENT_ID
, PRO_THIS.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO_PREV.PAY_PROPOSAL_ID PREV_PAY_PROPOSAL_ID
, PRO_THIS.PROPOSAL_REASON PROPOSAL_REASON
, PRO_PREV.PROPOSAL_REASON PREV_PROPOSAL_REASON
, PRO_THIS.MULTIPLE_COMPONENTS MULTIPLE_COMPONENTS
, PRO_THIS.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, DECODE(PRO_PREV.CHANGE_DATE
, NULL
, 'Y'
, 'N') LAST_SALARY_FLAG
, PRO_THIS.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM PER_PAY_PROPOSALS PRO_THIS
, PER_PAY_PROPOSALS PRO_PREV
WHERE PRO_THIS.APPROVED = 'Y'
AND PRO_PREV.APPROVED (+) = 'Y'
AND PRO_THIS.ASSIGNMENT_ID = PRO_PREV.ASSIGNMENT_ID (+)
AND PRO_THIS.CHANGE_DATE = PRO_PREV.LAST_CHANGE_DATE (+) ) PRO
, PER_ALL_ASSIGNMENTS_F ASG
, PER_PAY_BASES PPB
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PER_PERFORMANCE_REVIEWS PRV
WHERE ASG.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PRO.PERFORMANCE_REVIEW_ID = PRV.PERFORMANCE_REVIEW_ID (+)
AND PPB.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRO.EVENT_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND PRO.EVENT_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PRO.EVENT_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PRO.EVENT_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE WITH READ ONLY