VIEW: APPS.HRI_MD_SAL_GRDRT_X_V

Source

View Text - Preformatted

SELECT row_indicator , annualized_salary , grdrt_converted_annual_sal , salary , salary_annualization_factor , annualized_grade_max , annualized_grade_mid , annualized_grade_min , ((grdrt_converted_annual_sal/A.annualized_grade_mid)*100) grade_salary_comparatio , grade_max , grade_mid , grade_min , grade_annualization_factor , DECODE(SIGN(A.annualized_grade_max - grdrt_converted_annual_sal) , -1, 1 , 0) above_max_indicator , DECODE(SIGN(A.annualized_grade_max - grdrt_converted_annual_sal) , 1, 1 , 0) below_max_indicator , DECODE(SIGN(A.annualized_grade_mid - grdrt_converted_annual_sal) , -1, 1 , 0) above_mid_indicator , DECODE(SIGN(A.annualized_grade_mid - grdrt_converted_annual_sal) , 1, 1 , 0) below_mid_indicator , DECODE(SIGN(A.annualized_grade_min - grdrt_converted_annual_sal) , -1, 1 , 0) above_min_indicator , DECODE(SIGN(A.annualized_grade_min - grdrt_converted_annual_sal) , 1, 1 , 0) below_min_indicator , DECODE( (DECODE(DECODE(SIGN(grdrt_converted_annual_sal - annualized_grade_min), 1, 0, -1, 1), 0,'OVER_MIN_VALUE' , 0)) , 'OVER_MIN_VALUE', DECODE(SIGN(grdrt_converted_annual_sal - annualized_grade_mid), 1, 0, -1, 1, 0,1) ,1,1, 0) min_to_mid_indicator , DECODE( (DECODE(DECODE(SIGN(grdrt_converted_annual_sal - annualized_grade_mid), 1, 0, -1, 1), 0,'OVER_MID_VALUE' , 0)) , 'OVER_MID_VALUE', DECODE(SIGN(grdrt_converted_annual_sal - annualized_grade_max), 1, 0, -1, 1,0,1) ,1,1, 0) mid_to_max_indicator , DECODE(A.annualized_grade_min, NULL, 0, 0, 0, (grdrt_converted_annual_sal/A.annualized_grade_min) ) salary_to_min_ratio , DECODE(A.annualized_grade_mid, NULL, 0, 0, 0, (grdrt_converted_annual_sal/A.annualized_grade_mid)) salary_to_mid_ratio , DECODE(A.annualized_grade_max, NULL, 0, 0, 0, (grdrt_converted_annual_sal/A.annualized_grade_max)) salary_to_max_ratio , DECODE(A.annualized_grade_min, NULL, 0, 0, 0, (((grdrt_converted_annual_sal/A.annualized_grade_min)-1)*100)) salary_to_min_ratio_pct , DECODE(A.annualized_grade_mid, NULL, 0, 0, 0, (((grdrt_converted_annual_sal/A.annualized_grade_mid)-1)*100)) salary_to_mid_ratio_pct , DECODE(A.annualized_grade_max, NULL, 0, 0, 0, (((grdrt_converted_annual_sal/A.annualized_grade_max)-1)*100)) salary_to_max_ratio_pct , effective_start_date , effective_end_date , assignment_id , person_id , business_group_id , organization_id , location_id , job_id , grade_id , position_id , supervisor_person_id , pay_proposal_id , assignment_type_code , currency_code , output_currency_code , grade_currency_code , grdrate_output_currency_code , grade_rule_currency_code , performance_rating_code , multiple_component_flag_code , proposal_reason_code , last_salary_flag_code , grade_rule_id , rate_id , last_change_date , asg_last_update_date , pro_last_update_date , grl_last_update_date FROM (SELECT 1 row_indicator , sal.annualized_salary annualized_salary , decode(sal.currency_code, DECODE(grt.grade_rule_currency_code, 'NA_EDW', grt.output_currency_code, grt.grade_rule_currency_code) , sal.annualized_salary , hri_bpl_currency.convert_currency_amount ( sal.currency_code, DECODE(grt.grade_rule_currency_code, 'NA_EDW', grt.output_currency_code, grt.grade_rule_currency_code), SYSDATE, sal.annualized_salary, hr_currency_pkg.get_rate_type( sal.business_group_id , sal.effective_start_date , 'R') ) ) grdrt_converted_annual_sal , sal.salary salary , sal.annualization_factor salary_annualization_factor , grt.annualized_grade_max annualized_grade_max , grt.annualized_grade_mid annualized_grade_mid , grt.annualized_grade_min annualized_grade_min , grt.grade_max grade_max , grt.grade_mid grade_mid , grt.grade_min grade_min , grt.grade_annualization_factor grade_annualization_factor , sal.effective_start_date effective_start_date , sal.effective_end_date effective_end_date , sal.assignment_id assignment_id , sal.person_id person_id , sal.business_group_id business_group_id , sal.organization_id organization_id , sal.location_id location_id , sal.job_id job_id , sal.grade_id grade_id , sal.position_id position_id , sal.supervisor_person_id supervisor_person_id , sal.pay_proposal_id pay_proposal_id , sal.assignment_type_code assignment_type_code , sal.currency_code currency_code , sal.output_currency_code output_currency_code , grt.currency_code grade_currency_code , grt.output_currency_code grdrate_output_currency_code , grt.grade_rule_currency_code grade_rule_currency_code , sal.perf_rating_code performance_rating_code , sal.multiple_component_flag_code multiple_component_flag_code , sal.proposal_reason_code proposal_reason_code , sal.last_salary_flag_code last_salary_flag_code , grt.grade_rule_id grade_rule_id , grt.rate_id rate_id , GREATEST(sal.asg_last_update_date , sal.pro_last_update_date , grt.grl_last_update_date) last_change_date , sal.asg_last_update_date asg_last_update_date , sal.pro_last_update_date pro_last_update_date , grt.grl_last_update_date grl_last_update_date FROM hri_mb_salary_v sal , hri_mb_grade_rate_v grt WHERE trunc(sysdate) between sal.effective_start_date and sal.effective_end_date and sal.assignment_id = grt.assignment_id and trunc(sysdate) between grt.effective_start_date and grt.effective_end_date and sal.salary_basis_id = grt.pay_basis_id ) A

View Text - HTML Formatted

SELECT ROW_INDICATOR
, ANNUALIZED_SALARY
, GRDRT_CONVERTED_ANNUAL_SAL
, SALARY
, SALARY_ANNUALIZATION_FACTOR
, ANNUALIZED_GRADE_MAX
, ANNUALIZED_GRADE_MID
, ANNUALIZED_GRADE_MIN
, ((GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MID)*100) GRADE_SALARY_COMPARATIO
, GRADE_MAX
, GRADE_MID
, GRADE_MIN
, GRADE_ANNUALIZATION_FACTOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MAX - GRDRT_CONVERTED_ANNUAL_SAL)
, -1
, 1
, 0) ABOVE_MAX_INDICATOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MAX - GRDRT_CONVERTED_ANNUAL_SAL)
, 1
, 1
, 0) BELOW_MAX_INDICATOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MID - GRDRT_CONVERTED_ANNUAL_SAL)
, -1
, 1
, 0) ABOVE_MID_INDICATOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MID - GRDRT_CONVERTED_ANNUAL_SAL)
, 1
, 1
, 0) BELOW_MID_INDICATOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MIN - GRDRT_CONVERTED_ANNUAL_SAL)
, -1
, 1
, 0) ABOVE_MIN_INDICATOR
, DECODE(SIGN(A.ANNUALIZED_GRADE_MIN - GRDRT_CONVERTED_ANNUAL_SAL)
, 1
, 1
, 0) BELOW_MIN_INDICATOR
, DECODE( (DECODE(DECODE(SIGN(GRDRT_CONVERTED_ANNUAL_SAL - ANNUALIZED_GRADE_MIN)
, 1
, 0
, -1
, 1)
, 0
, 'OVER_MIN_VALUE'
, 0))
, 'OVER_MIN_VALUE'
, DECODE(SIGN(GRDRT_CONVERTED_ANNUAL_SAL - ANNUALIZED_GRADE_MID)
, 1
, 0
, -1
, 1
, 0
, 1)
, 1
, 1
, 0) MIN_TO_MID_INDICATOR
, DECODE( (DECODE(DECODE(SIGN(GRDRT_CONVERTED_ANNUAL_SAL - ANNUALIZED_GRADE_MID)
, 1
, 0
, -1
, 1)
, 0
, 'OVER_MID_VALUE'
, 0))
, 'OVER_MID_VALUE'
, DECODE(SIGN(GRDRT_CONVERTED_ANNUAL_SAL - ANNUALIZED_GRADE_MAX)
, 1
, 0
, -1
, 1
, 0
, 1)
, 1
, 1
, 0) MID_TO_MAX_INDICATOR
, DECODE(A.ANNUALIZED_GRADE_MIN
, NULL
, 0
, 0
, 0
, (GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MIN) ) SALARY_TO_MIN_RATIO
, DECODE(A.ANNUALIZED_GRADE_MID
, NULL
, 0
, 0
, 0
, (GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MID)) SALARY_TO_MID_RATIO
, DECODE(A.ANNUALIZED_GRADE_MAX
, NULL
, 0
, 0
, 0
, (GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MAX)) SALARY_TO_MAX_RATIO
, DECODE(A.ANNUALIZED_GRADE_MIN
, NULL
, 0
, 0
, 0
, (((GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MIN)-1)*100)) SALARY_TO_MIN_RATIO_PCT
, DECODE(A.ANNUALIZED_GRADE_MID
, NULL
, 0
, 0
, 0
, (((GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MID)-1)*100)) SALARY_TO_MID_RATIO_PCT
, DECODE(A.ANNUALIZED_GRADE_MAX
, NULL
, 0
, 0
, 0
, (((GRDRT_CONVERTED_ANNUAL_SAL/A.ANNUALIZED_GRADE_MAX)-1)*100)) SALARY_TO_MAX_RATIO_PCT
, EFFECTIVE_START_DATE
, EFFECTIVE_END_DATE
, ASSIGNMENT_ID
, PERSON_ID
, BUSINESS_GROUP_ID
, ORGANIZATION_ID
, LOCATION_ID
, JOB_ID
, GRADE_ID
, POSITION_ID
, SUPERVISOR_PERSON_ID
, PAY_PROPOSAL_ID
, ASSIGNMENT_TYPE_CODE
, CURRENCY_CODE
, OUTPUT_CURRENCY_CODE
, GRADE_CURRENCY_CODE
, GRDRATE_OUTPUT_CURRENCY_CODE
, GRADE_RULE_CURRENCY_CODE
, PERFORMANCE_RATING_CODE
, MULTIPLE_COMPONENT_FLAG_CODE
, PROPOSAL_REASON_CODE
, LAST_SALARY_FLAG_CODE
, GRADE_RULE_ID
, RATE_ID
, LAST_CHANGE_DATE
, ASG_LAST_UPDATE_DATE
, PRO_LAST_UPDATE_DATE
, GRL_LAST_UPDATE_DATE
FROM (SELECT 1 ROW_INDICATOR
, SAL.ANNUALIZED_SALARY ANNUALIZED_SALARY
, DECODE(SAL.CURRENCY_CODE
, DECODE(GRT.GRADE_RULE_CURRENCY_CODE
, 'NA_EDW'
, GRT.OUTPUT_CURRENCY_CODE
, GRT.GRADE_RULE_CURRENCY_CODE)
, SAL.ANNUALIZED_SALARY
, HRI_BPL_CURRENCY.CONVERT_CURRENCY_AMOUNT ( SAL.CURRENCY_CODE
, DECODE(GRT.GRADE_RULE_CURRENCY_CODE
, 'NA_EDW'
, GRT.OUTPUT_CURRENCY_CODE
, GRT.GRADE_RULE_CURRENCY_CODE)
, SYSDATE
, SAL.ANNUALIZED_SALARY
, HR_CURRENCY_PKG.GET_RATE_TYPE( SAL.BUSINESS_GROUP_ID
, SAL.EFFECTIVE_START_DATE
, 'R') ) ) GRDRT_CONVERTED_ANNUAL_SAL
, SAL.SALARY SALARY
, SAL.ANNUALIZATION_FACTOR SALARY_ANNUALIZATION_FACTOR
, GRT.ANNUALIZED_GRADE_MAX ANNUALIZED_GRADE_MAX
, GRT.ANNUALIZED_GRADE_MID ANNUALIZED_GRADE_MID
, GRT.ANNUALIZED_GRADE_MIN ANNUALIZED_GRADE_MIN
, GRT.GRADE_MAX GRADE_MAX
, GRT.GRADE_MID GRADE_MID
, GRT.GRADE_MIN GRADE_MIN
, GRT.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, SAL.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, SAL.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, SAL.ASSIGNMENT_ID ASSIGNMENT_ID
, SAL.PERSON_ID PERSON_ID
, SAL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, SAL.ORGANIZATION_ID ORGANIZATION_ID
, SAL.LOCATION_ID LOCATION_ID
, SAL.JOB_ID JOB_ID
, SAL.GRADE_ID GRADE_ID
, SAL.POSITION_ID POSITION_ID
, SAL.SUPERVISOR_PERSON_ID SUPERVISOR_PERSON_ID
, SAL.PAY_PROPOSAL_ID PAY_PROPOSAL_ID
, SAL.ASSIGNMENT_TYPE_CODE ASSIGNMENT_TYPE_CODE
, SAL.CURRENCY_CODE CURRENCY_CODE
, SAL.OUTPUT_CURRENCY_CODE OUTPUT_CURRENCY_CODE
, GRT.CURRENCY_CODE GRADE_CURRENCY_CODE
, GRT.OUTPUT_CURRENCY_CODE GRDRATE_OUTPUT_CURRENCY_CODE
, GRT.GRADE_RULE_CURRENCY_CODE GRADE_RULE_CURRENCY_CODE
, SAL.PERF_RATING_CODE PERFORMANCE_RATING_CODE
, SAL.MULTIPLE_COMPONENT_FLAG_CODE MULTIPLE_COMPONENT_FLAG_CODE
, SAL.PROPOSAL_REASON_CODE PROPOSAL_REASON_CODE
, SAL.LAST_SALARY_FLAG_CODE LAST_SALARY_FLAG_CODE
, GRT.GRADE_RULE_ID GRADE_RULE_ID
, GRT.RATE_ID RATE_ID
, GREATEST(SAL.ASG_LAST_UPDATE_DATE
, SAL.PRO_LAST_UPDATE_DATE
, GRT.GRL_LAST_UPDATE_DATE) LAST_CHANGE_DATE
, SAL.ASG_LAST_UPDATE_DATE ASG_LAST_UPDATE_DATE
, SAL.PRO_LAST_UPDATE_DATE PRO_LAST_UPDATE_DATE
, GRT.GRL_LAST_UPDATE_DATE GRL_LAST_UPDATE_DATE
FROM HRI_MB_SALARY_V SAL
, HRI_MB_GRADE_RATE_V GRT
WHERE TRUNC(SYSDATE) BETWEEN SAL.EFFECTIVE_START_DATE
AND SAL.EFFECTIVE_END_DATE
AND SAL.ASSIGNMENT_ID = GRT.ASSIGNMENT_ID
AND TRUNC(SYSDATE) BETWEEN GRT.EFFECTIVE_START_DATE
AND GRT.EFFECTIVE_END_DATE
AND SAL.SALARY_BASIS_ID = GRT.PAY_BASIS_ID ) A