1. Tables and Views for HCM
  2. CMP_PRE_SALARY_V

CMP_PRE_SALARY_V

Details

  • Schema: FUSION

  • Object owner: CMP

  • Object type: VIEW

Columns

Name

SALARY_ID

ASSIGNMENT_ID

DATE_FROM

DATE_TO

SALARY_AMOUNT

ELEMENT_TYPE_ID

GEOGRAPHY_ID

GEOGRAPHY_TYPE_ID

ZONE_GRADE_RATE_ID

BUSINESS_UNIT_ID

JOB_ID

WORK_AT_HOME

LAST_UPDATE_DATE

GRADE_RATE_MINIMUM_LIMIT

RATE_NAME

RATE_ID

ANNUALIZATION_FACTOR

RATE_TYPE

RATE_OBJECT_TYPE

RATE_FREQUENCY

RATE_VALUE

PAYROLL_FREQUENCY_CODE

PAYROLL_ANNUAL_FACTOR

PREV_AMT

SALARY_CHANGE_AMT

SALARY_CHANGE_PER

PREV_SALARY_BASIS

PREV_CURR_CODE

MINIMUM

MID_VALUE

MAXIMUM

QUARTILE

QUINTILE

PERCENTILE

COMPARATIO

ANNUALIZED_SALARY

ANNUALIZED_FULLTIME_SALARY

RANGE_DIFF_FACTOR

Query

SQL_Statement

SELECT salary.SALARY_ID,

salary.ASSIGNMENT_ID,

salary.DATE_FROM,

salary.DATE_TO ,

salary.SALARY_AMOUNT,

salary.ELEMENT_TYPE_ID,

salary.GEOGRAPHY_ID,

salary.GEOGRAPHY_TYPE_ID,

salary.ZONE_GRADE_RATE_ID,

salary.BUSINESS_UNIT_ID,

salary.JOB_ID,

salary.WORK_AT_HOME,

salary.LAST_UPDATE_DATE,

salary.GRADE_RATE_MINIMUM_LIMIT,

RAT.NAME Rate_Name,

RAT.RATE_ID,

RAT.ANNUALIZATION_FACTOR,

RAT.RATE_TYPE,

RAT.RATE_OBJECT_TYPE,

RAT.RATE_FREQUENCY,

cmp_ff_dbi_pkg.get_grade_def(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) rate_value,

decode(salary.SALARY_BASIS_CODE,'PERIOD',upper(cmp_ff_dbi_pkg.get_payroll_freq_code(salary.ASSIGNMENT_ID, greatest(salary.date_from,least(trunc(sysdate),salary.date_to)))) , salary.SALARY_BASIS_CODE) payroll_frequency_code,

cmp_ff_dbi_pkg.get_payroll_factor( salary.ASSIGNMENT_ID,

salary.SALARY_BASIS_ID ,

greatest(salary.date_from,least(trunc(sysdate),salary.date_to))) payroll_annual_factor,

cmp_ff_dbi_pkg.get_previous_sal(salary.ASSIGNMENT_ID,salary.DATE_FROM) Prev_Amt,

cmp_ff_dbi_pkg.get_salary_adjustment_amount(salary.SALARY_ID,salary.DATE_FROM) Salary_Change_Amt,

cmp_ff_dbi_pkg.get_salary_adjustment_percent(salary.SALARY_ID,salary.DATE_FROM) Salary_change_per,

cmp_ff_dbi_pkg.get_prev_sal_basis_id(salary.ASSIGNMENT_ID,salary.DATE_FROM) prev_salary_basis,

cmp_ff_dbi_pkg.get_prev_curr_code(salary.ASSIGNMENT_ID,salary.DATE_FROM) prev_curr_code,

cmp_ff_dbi_pkg.get_grade_min(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) minimum,

cmp_ff_dbi_pkg.get_grade_mid(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) mid_value,

cmp_ff_dbi_pkg.get_grade_max(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) maximum,

cmp_ff_dbi_pkg.get_quartile(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) quartile,

cmp_ff_dbi_pkg.get_quintile(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) quintile,

cmp_ff_dbi_pkg.get_percentile_value_fuse(salary.ASSIGNMENT_ID,salary.SALARY_BASIS_ID,salary.SALARY_AMOUNT,RAT.RATE_ID,salary.GRADE_ID,RAT.ANNUALIZATION_FACTOR,salary.RATE_DATE) percentile,

cmp_ff_dbi_pkg.get_comparatio(salary.grade_id,salary.salary_id,salary.ASSIGNMENT_ID,salary.RATE_DATE,nvl(salary.value,1)) comparatio,

cmp_ff_dbi_pkg.GET_ANNUAL_SALARY_AMOUNT(salary.ASSIGNMENT_ID,greatest(salary.date_from,least(sysdate,salary.date_to))) ANNUALIZED_SALARY,

cmp_ff_dbi_pkg.GET_ANNUAL_FT_SALARY_AMOUNT(salary.ASSIGNMENT_ID,greatest(salary.date_from,least(sysdate,salary.date_to))) ANNUALIZED_FULLTIME_SALARY,

cmp_ff_dbi_pkg.get_range_differential_factor(salary.SALARY_ID,salary.RATE_DATE) RANGE_DIFF_FACTOR

FROM

(SELECT sal.SALARY_ID,

sal.ASSIGNMENT_ID,

sal.DATE_FROM,

sal.DATE_TO ,

sal.SALARY_AMOUNT,

sal.GEOGRAPHY_ID,

sal.GEOGRAPHY_TYPE_ID,

sal.ZONE_GRADE_RATE_ID,

sal.BUSINESS_UNIT_ID,

sal.JOB_ID,

sal.WORK_AT_HOME,

sal.GRADE_RATE_MINIMUM_LIMIT,

sal.LAST_UPDATE_DATE,

sb.SALARY_BASIS_ID,

sb.salary_basis_code,

sb.GRADE_RATE_ID,

sb.ELEMENT_TYPE_ID,

sb.SALARY_ANNUALIZATION_FACTOR,

asg.GRADE_ID,

greatest(sal.date_from,least(trunc(sysdate),sal.date_to)) RATE_DATE,

fte.VALUE

FROM CMP_SALARY sal ,

CMP_SALARY_BASES SB ,

PER_ALL_ASSIGNMENTS_M asg,

PER_ASSIGN_WORK_MEASURES_F fte

WHERE sal.SALARY_BASIS_ID = SB.SALARY_BASIS_ID

AND sal.ASSIGNMENT_ID = asg.ASSIGNMENT_ID

AND asg.EFFECTIVE_LATEST_CHANGE = 'Y'

AND greatest(sal.date_from,least(trunc(sysdate),sal.date_to)) BETWEEN asg.EFFECTIVE_START_DATE AND asg.EFFECTIVE_END_DATE

AND sal.ASSIGNMENT_ID = fte.ASSIGNMENT_ID(+)

AND fte.unit(+) ='FTE'

AND greatest(sal.date_from,least(trunc(sysdate),sal.date_to)) BETWEEN fte.EFFECTIVE_START_DATE(+) AND fte.EFFECTIVE_END_DATE(+)

) salary,

per_rates_f_vl RAT

WHERE salary.GRADE_RATE_ID = RAT.RATE_ID (+)

AND greatest(salary.date_from,least(trunc(sysdate),salary.date_to)) BETWEEN RAT.EFFECTIVE_START_DATE(+) AND RAT.EFFECTIVE_END_DATE(+)