VIEW: APPS.HRI_MB_SALARY_V

Source

View Text - Preformatted

SELECT to_number(ppb.pay_annualization_factor * pro.proposed_salary_n) annualized_salary , pro.proposed_salary_n salary , ppb.pay_annualization_factor annualization_factor , GREATEST(asg.effective_start_date ,pro.effective_start_date ) effective_start_date , LEAST(asg.effective_end_date ,pro.effective_end_date ) effective_end_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.payroll_id,-1) payroll_id , asg.pay_basis_id salary_basis_id , NVL(asg.pay_basis_id,-1) people_group_id , NVL(asg.supervisor_id,-1) supervisor_person_id ,asg.supervisor_id supervisor_person_id_n , asg.assignment_type assignment_type_code , asg.assignment_status_type_id assignment_status_type_id , ast.per_system_status per_system_status_code , ast.pay_system_status pay_system_status_code , NVL(asg.recruiter_id,-1) recruiter_id , NVL(asg.set_of_books_id,-1) set_of_books_id , asg.period_of_service_id period_of_service_id , asg.employment_category employment_category_code , asg.primary_flag primary_flag_code , NVL(pro.pay_proposal_id,-1) pay_proposal_id , 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_component_flag_code , NVL(pro.proposal_reason,'NA_EDW') proposal_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 , asg.effective_start_date asg_effective_start_date , asg.effective_end_date asg_effective_end_date , pro.effective_start_date pro_effective_start_date , pro.effective_end_date pro_effective_end_date FROM per_all_assignments_f asg , per_assignment_status_types ast , per_periods_of_service pps , (SELECT pro_this.proposed_salary_n proposed_salary_n , pro_this.change_date effective_start_date , NVL(pro_next.change_date - 1 ,hr_general.end_of_time) effective_end_date , pro_this.assignment_id assignment_id , pro_this.pay_proposal_id pay_proposal_id , pro_this.proposal_reason proposal_reason , pro_this.multiple_components multiple_components , pro_next.pay_proposal_id next_pay_proposal_id , pro_this.performance_review_id performance_review_id , DECODE(pro_next.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_next WHERE pro_this.approved = 'Y' AND pro_next.approved (+) = 'Y' AND pro_this.assignment_id = pro_next.assignment_id (+) AND pro_this.change_date = pro_next.last_change_date (+) ) pro , per_pay_bases ppb , pay_input_values_f piv , pay_element_types_f pet , per_performance_reviews prv WHERE asg.assignment_type = 'E' AND asg.period_of_service_id = pps.period_of_service_id AND asg.assignment_id = pro.assignment_id AND asg.assignment_status_type_id = ast.assignment_status_type_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 ( pps.actual_termination_date IS NULL OR pps.actual_termination_date >= GREATEST(pro.effective_start_date ,asg.effective_start_date) ) AND pro.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pro.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND ( (pro.effective_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date ) OR (asg.effective_start_date BETWEEN pro.effective_start_date AND pro.effective_end_date ) ) WITH READ ONLY

View Text - HTML Formatted

SELECT TO_NUMBER(PPB.PAY_ANNUALIZATION_FACTOR * PRO.PROPOSED_SALARY_N) ANNUALIZED_SALARY
, PRO.PROPOSED_SALARY_N SALARY
, PPB.PAY_ANNUALIZATION_FACTOR ANNUALIZATION_FACTOR
, GREATEST(ASG.EFFECTIVE_START_DATE
, PRO.EFFECTIVE_START_DATE ) EFFECTIVE_START_DATE
, LEAST(ASG.EFFECTIVE_END_DATE
, PRO.EFFECTIVE_END_DATE ) EFFECTIVE_END_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.PAYROLL_ID
, -1) PAYROLL_ID
, ASG.PAY_BASIS_ID SALARY_BASIS_ID
, NVL(ASG.PAY_BASIS_ID
, -1) PEOPLE_GROUP_ID
, NVL(ASG.SUPERVISOR_ID
, -1) SUPERVISOR_PERSON_ID
, ASG.SUPERVISOR_ID SUPERVISOR_PERSON_ID_N
, ASG.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_CODE
, ASG.ASSIGNMENT_STATUS_TYPE_ID ASSIGNMENT_STATUS_TYPE_ID
, AST.PER_SYSTEM_STATUS PER_SYSTEM_STATUS_CODE
, AST.PAY_SYSTEM_STATUS PAY_SYSTEM_STATUS_CODE
, NVL(ASG.RECRUITER_ID
, -1) RECRUITER_ID
, NVL(ASG.SET_OF_BOOKS_ID
, -1) SET_OF_BOOKS_ID
, ASG.PERIOD_OF_SERVICE_ID PERIOD_OF_SERVICE_ID
, ASG.EMPLOYMENT_CATEGORY EMPLOYMENT_CATEGORY_CODE
, ASG.PRIMARY_FLAG PRIMARY_FLAG_CODE
, NVL(PRO.PAY_PROPOSAL_ID
, -1) PAY_PROPOSAL_ID
, 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_COMPONENT_FLAG_CODE
, NVL(PRO.PROPOSAL_REASON
, 'NA_EDW') PROPOSAL_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
, ASG.EFFECTIVE_START_DATE ASG_EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE ASG_EFFECTIVE_END_DATE
, PRO.EFFECTIVE_START_DATE PRO_EFFECTIVE_START_DATE
, PRO.EFFECTIVE_END_DATE PRO_EFFECTIVE_END_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_ASSIGNMENT_STATUS_TYPES AST
, PER_PERIODS_OF_SERVICE PPS
, (SELECT PRO_THIS.PROPOSED_SALARY_N PROPOSED_SALARY_N
, PRO_THIS.CHANGE_DATE EFFECTIVE_START_DATE
, NVL(PRO_NEXT.CHANGE_DATE - 1
, HR_GENERAL.END_OF_TIME) EFFECTIVE_END_DATE
, PRO_THIS.ASSIGNMENT_ID ASSIGNMENT_ID
, PRO_THIS.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, PRO_THIS.PROPOSAL_REASON PROPOSAL_REASON
, PRO_THIS.MULTIPLE_COMPONENTS MULTIPLE_COMPONENTS
, PRO_NEXT.PAY_PROPOSAL_ID NEXT_PAY_PROPOSAL_ID
, PRO_THIS.PERFORMANCE_REVIEW_ID PERFORMANCE_REVIEW_ID
, DECODE(PRO_NEXT.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_NEXT
WHERE PRO_THIS.APPROVED = 'Y'
AND PRO_NEXT.APPROVED (+) = 'Y'
AND PRO_THIS.ASSIGNMENT_ID = PRO_NEXT.ASSIGNMENT_ID (+)
AND PRO_THIS.CHANGE_DATE = PRO_NEXT.LAST_CHANGE_DATE (+) ) PRO
, PER_PAY_BASES PPB
, PAY_INPUT_VALUES_F PIV
, PAY_ELEMENT_TYPES_F PET
, PER_PERFORMANCE_REVIEWS PRV
WHERE ASG.ASSIGNMENT_TYPE = 'E'
AND ASG.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND ASG.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_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 ( PPS.ACTUAL_TERMINATION_DATE IS NULL OR PPS.ACTUAL_TERMINATION_DATE >= GREATEST(PRO.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_START_DATE) )
AND PRO.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PRO.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND ( (PRO.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE ) OR (ASG.EFFECTIVE_START_DATE BETWEEN PRO.EFFECTIVE_START_DATE
AND PRO.EFFECTIVE_END_DATE ) ) WITH READ ONLY