CMP_SALARY_HISTORY

Details

Schema: FUSION
Object owner: CMP
Object type: VIEW

Columns

Name
BUSINESS_GROUP_ID ASSIGNMENT_ID SALARY_ID SALARY_BASIS_ID ELEMENT_ENTRY_ID DATE_FROM DATE_TO HISTORY_DATE SALARY_REASON_CODE SALARY_AMOUNT SALARY_APPROVED MULTIPLE_COMPONENTS PERFORMANCE_REVIEW_ID REVIEW_DATE PERFORMANCE_RATING NEXT_PERF_REVIEW_DATE NEXT_SAL_REVIEW_DATE FORCED_RANKING OBJECT_VERSION_NUMBER LAST_UPDATE_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN CREATED_BY CREATION_DATE ACTION_OCCURRENCE_ID ACTION_ID ACTION_REASON_ID GEOGRAPHY_ID GEOGRAPHY_TYPE_ID ZONE_GRADE_RATE_ID BUSINESS_UNIT_ID JOB_ID WORK_AT_HOME GRADE_RATE_MINIMUM_LIMIT SALARY_BASIS_NAME DESCRIPTION SB_LANGUAGE SB_SOUCE_LANG SB_SALARY_BASIS_ID SB_BUSINESS_GROUP_ID SALARY_BASES_NAME SALARY_BASIS_CODE RANGE_ERROR_WARNING CODE SALARY_ANNUALIZATION_FACTOR PAYROLL_FREQUENCY_CODE PAYROLL_ANNUAL_FACTOR LEGAL_ENTITY_ID GRADE_RATE_ID ELEMENT_TYPE_ID INPUT_VALUE_ID LEGISLATIVE_DATA_GROUP_ID COMPONENT_USAGE ANNUALIZED_HOURS GRADE_ANNUALIZATION_FACTOR SALARY_BASIS_CREATED_BY SALARY_BASIS_CREATION_DATE SALARY_BASIS_LAST_UPDATE_DATE SALARY_BASIS_LAST_UPDATED_BY STATUS SALARY_BASIS_TYPE AMOUNT_DECIMAL_PRECISION AMOUNT_ROUNDING_CODE ANNUAL_ROUNDING_CODE RANGE_ROUNDING_CODE CALCULATION_MODE INPUT_VALUE_NAME INPUT_VALUE_LANGUAGE SOURCE_LANG ANNUALIZED_SALARY ANNUALIZED_FULLTIME_SALARY

Name

BUSINESS_GROUP_ID

ASSIGNMENT_ID

SALARY_ID

SALARY_BASIS_ID

ELEMENT_ENTRY_ID

DATE_FROM

DATE_TO

HISTORY_DATE

SALARY_REASON_CODE

SALARY_AMOUNT

SALARY_APPROVED

MULTIPLE_COMPONENTS

PERFORMANCE_REVIEW_ID

REVIEW_DATE

PERFORMANCE_RATING

NEXT_PERF_REVIEW_DATE

NEXT_SAL_REVIEW_DATE

FORCED_RANKING

OBJECT_VERSION_NUMBER

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

CREATED_BY

CREATION_DATE

ACTION_OCCURRENCE_ID

ACTION_ID

ACTION_REASON_ID

GEOGRAPHY_ID

GEOGRAPHY_TYPE_ID

ZONE_GRADE_RATE_ID

BUSINESS_UNIT_ID

JOB_ID

WORK_AT_HOME

GRADE_RATE_MINIMUM_LIMIT

SALARY_BASIS_NAME

DESCRIPTION

SB_LANGUAGE

SB_SOUCE_LANG

SB_SALARY_BASIS_ID

SB_BUSINESS_GROUP_ID

SALARY_BASES_NAME

SALARY_BASIS_CODE

RANGE_ERROR_WARNING

CODE

SALARY_ANNUALIZATION_FACTOR

PAYROLL_FREQUENCY_CODE

PAYROLL_ANNUAL_FACTOR

LEGAL_ENTITY_ID

GRADE_RATE_ID

ELEMENT_TYPE_ID

INPUT_VALUE_ID

LEGISLATIVE_DATA_GROUP_ID

COMPONENT_USAGE

ANNUALIZED_HOURS

GRADE_ANNUALIZATION_FACTOR

SALARY_BASIS_CREATED_BY

SALARY_BASIS_CREATION_DATE

SALARY_BASIS_LAST_UPDATE_DATE

SALARY_BASIS_LAST_UPDATED_BY

STATUS

SALARY_BASIS_TYPE

AMOUNT_DECIMAL_PRECISION

AMOUNT_ROUNDING_CODE

ANNUAL_ROUNDING_CODE

RANGE_ROUNDING_CODE

CALCULATION_MODE

INPUT_VALUE_NAME

INPUT_VALUE_LANGUAGE

SOURCE_LANG

ANNUALIZED_SALARY

ANNUALIZED_FULLTIME_SALARY

Query

SQL_Statement
Select SALARYPEO.BUSINESS_GROUP_ID , SALARYPEO.ASSIGNMENT_ID , SALARYPEO.SALARY_ID , SALARYPEO.SALARY_BASIS_ID , SALARYPEO.ELEMENT_ENTRY_ID , SALARYPEO.DATE_FROM , SALARYPEO.DATE_TO , greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to)) history_date, SALARYPEO.SALARY_REASON_CODE , SALARYPEO.SALARY_AMOUNT , SALARYPEO.SALARY_APPROVED , SALARYPEO.MULTIPLE_COMPONENTS , SALARYPEO.PERFORMANCE_REVIEW_ID , SALARYPEO.REVIEW_DATE , SALARYPEO.PERFORMANCE_RATING , SALARYPEO.NEXT_PERF_REVIEW_DATE , SALARYPEO.NEXT_SAL_REVIEW_DATE , SALARYPEO.FORCED_RANKING , SALARYPEO.OBJECT_VERSION_NUMBER , SALARYPEO.LAST_UPDATE_DATE , SALARYPEO.LAST_UPDATED_BY , SALARYPEO.LAST_UPDATE_LOGIN , SALARYPEO.CREATED_BY , SALARYPEO.CREATION_DATE , SALARYPEO.ACTION_OCCURRENCE_ID , SALARYPEO.ACTION_ID , SALARYPEO.ACTION_REASON_ID , SALARYPEO.GEOGRAPHY_ID, SALARYPEO.GEOGRAPHY_TYPE_ID, SALARYPEO.ZONE_GRADE_RATE_ID, SALARYPEO.BUSINESS_UNIT_ID, SALARYPEO.JOB_ID, SALARYPEO.WORK_AT_HOME, SALARYPEO.GRADE_RATE_MINIMUM_LIMIT, SALARYBASESTLPEO.SALARY_BASIS_NAME , SALARYBASESTLPEO.DESCRIPTION , SALARYBASESTLPEO.LANGUAGE SB_LANGUAGE , SALARYBASESTLPEO.SOURCE_LANG SB_SOUCE_LANG , SALARYBASESPEO.SALARY_BASIS_ID SB_SALARY_BASIS_ID , SALARYBASESPEO.BUSINESS_GROUP_ID SB_BUSINESS_GROUP_ID , SALARYBASESPEO.NAME SALARY_BASES_NAME , SALARYBASESPEO.SALARY_BASIS_CODE , SALARYBASESPEO.RANGE_ERROR_WARNING , SALARYBASESPEO.CODE , SALARYBASESPEO.SALARY_ANNUALIZATION_FACTOR , decode(SALARYBASESPEO.SALARY_BASIS_CODE,'PERIOD', upper(cmp_ff_dbi_pkg.get_payroll_freq_code( SALARYPEO.ASSIGNMENT_ID , greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to)))) , SALARYBASESPEO.SALARY_BASIS_CODE) payroll_frequency_code, cmp_ff_dbi_pkg.get_payroll_factor( SALARYPEO.ASSIGNMENT_ID , SALARYPEO.SALARY_BASIS_ID , greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to))) payroll_annual_factor, SALARYBASESPEO.LEGAL_ENTITY_ID , SALARYBASESPEO.GRADE_RATE_ID , SALARYBASESPEO.ELEMENT_TYPE_ID , SALARYBASESPEO.INPUT_VALUE_ID , SALARYBASESPEO.LEGISLATIVE_DATA_GROUP_ID , SALARYBASESPEO.COMPONENT_USAGE , SALARYBASESPEO.ANNUALIZED_HOURS, SALARYBASESPEO.GRADE_ANNUALIZATION_FACTOR, SALARYBASESPEO.CREATED_BY SALARY_BASIS_CREATED_BY , SALARYBASESPEO.CREATION_DATE SALARY_BASIS_CREATION_DATE, SALARYBASESPEO.LAST_UPDATE_DATE SALARY_BASIS_LAST_UPDATE_DATE, SALARYBASESPEO.LAST_UPDATED_BY SALARY_BASIS_LAST_UPDATED_BY, SALARYBASESPEO.STATUS, SALARYBASESPEO.SALARY_BASIS_TYPE, SALARYBASESPEO.AMOUNT_DECIMAL_PRECISION, SALARYBASESPEO.AMOUNT_ROUNDING_CODE, SALARYBASESPEO.ANNUAL_ROUNDING_CODE, SALARYBASESPEO.RANGE_ROUNDING_CODE, SALARYBASESPEO.CALCULATION_MODE, InputValueTranslationPEO.Name INPUT_VALUE_NAME, InputValueTranslationPEO.Language INPUT_VALUE_LANGUAGE, InputValueTranslationPEO.Source_Lang, cmp_ff_dbi_pkg.GET_ANNUAL_SALARY_AMOUNT(SALARYPEO.ASSIGNMENT_ID,greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to))) ANNUALIZED_SALARY, cmp_ff_dbi_pkg.GET_ANNUAL_FT_SALARY_AMOUNT(SALARYPEO.ASSIGNMENT_ID,greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to)))ANNUALIZED_FULLTIME_SALARY From Cmp_salary SALARYPEO, pay_input_values_tl InputValueTranslationPEO,CMP_SALARY_BASES SALARYBASESPEO,CMP_SALARY_BASES_TL SALARYBASESTLPEO Where SALARYPEO.SALARY_BASIS_ID = SALARYBASESPEO.SALARY_BASIS_ID AND SALARYBASESPEO.INPUT_VALUE_ID = InputValueTranslationPEO.INPUT_VALUE_ID(+) AND InputValueTranslationPEO.LANGUAGE(+) = USERENV('LANG') AND SALARYBASESPEO.SALARY_BASIS_ID = SALARYBASESTLPEO.SALARY_BASIS_ID AND SALARYBASESTLPEO.LANGUAGE = USERENV('LANG') AND SALARYBASESTLPEO.BUSINESS_GROUP_ID = SALARYBASESPEO.BUSINESS_GROUP_ID

SQL_Statement

Select

SALARYPEO.BUSINESS_GROUP_ID ,

SALARYPEO.ASSIGNMENT_ID ,

SALARYPEO.SALARY_ID ,

SALARYPEO.SALARY_BASIS_ID ,

SALARYPEO.ELEMENT_ENTRY_ID ,

SALARYPEO.DATE_FROM ,

SALARYPEO.DATE_TO ,

greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to)) history_date,

SALARYPEO.SALARY_REASON_CODE ,

SALARYPEO.SALARY_AMOUNT ,

SALARYPEO.SALARY_APPROVED ,

SALARYPEO.MULTIPLE_COMPONENTS ,

SALARYPEO.PERFORMANCE_REVIEW_ID ,

SALARYPEO.REVIEW_DATE ,

SALARYPEO.PERFORMANCE_RATING ,

SALARYPEO.NEXT_PERF_REVIEW_DATE ,

SALARYPEO.NEXT_SAL_REVIEW_DATE ,

SALARYPEO.FORCED_RANKING ,

SALARYPEO.OBJECT_VERSION_NUMBER ,

SALARYPEO.LAST_UPDATE_DATE ,

SALARYPEO.LAST_UPDATED_BY ,

SALARYPEO.LAST_UPDATE_LOGIN ,

SALARYPEO.CREATED_BY ,

SALARYPEO.CREATION_DATE ,

SALARYPEO.ACTION_OCCURRENCE_ID ,

SALARYPEO.ACTION_ID ,

SALARYPEO.ACTION_REASON_ID ,

SALARYPEO.GEOGRAPHY_ID,

SALARYPEO.GEOGRAPHY_TYPE_ID,

SALARYPEO.ZONE_GRADE_RATE_ID,

SALARYPEO.BUSINESS_UNIT_ID,

SALARYPEO.JOB_ID,

SALARYPEO.WORK_AT_HOME,

SALARYPEO.GRADE_RATE_MINIMUM_LIMIT,

SALARYBASESTLPEO.SALARY_BASIS_NAME ,

SALARYBASESTLPEO.DESCRIPTION ,

SALARYBASESTLPEO.LANGUAGE SB_LANGUAGE ,

SALARYBASESTLPEO.SOURCE_LANG SB_SOUCE_LANG ,

SALARYBASESPEO.SALARY_BASIS_ID SB_SALARY_BASIS_ID ,

SALARYBASESPEO.BUSINESS_GROUP_ID SB_BUSINESS_GROUP_ID ,

SALARYBASESPEO.NAME SALARY_BASES_NAME ,

SALARYBASESPEO.SALARY_BASIS_CODE ,

SALARYBASESPEO.RANGE_ERROR_WARNING ,

SALARYBASESPEO.CODE ,

SALARYBASESPEO.SALARY_ANNUALIZATION_FACTOR ,

decode(SALARYBASESPEO.SALARY_BASIS_CODE,'PERIOD',

upper(cmp_ff_dbi_pkg.get_payroll_freq_code( SALARYPEO.ASSIGNMENT_ID ,

greatest(SALARYPEO.date_from,least(trunc(sysdate),SALARYPEO.date_to)))) , SALARYBASESPEO.SALARY_BASIS_CODE) payroll_frequency_code,

cmp_ff_dbi_pkg.get_payroll_factor( SALARYPEO.ASSIGNMENT_ID ,

SALARYPEO.SALARY_BASIS_ID ,

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

SALARYBASESPEO.LEGAL_ENTITY_ID ,

SALARYBASESPEO.GRADE_RATE_ID ,

SALARYBASESPEO.ELEMENT_TYPE_ID ,

SALARYBASESPEO.INPUT_VALUE_ID ,

SALARYBASESPEO.LEGISLATIVE_DATA_GROUP_ID ,

SALARYBASESPEO.COMPONENT_USAGE ,

SALARYBASESPEO.ANNUALIZED_HOURS,

SALARYBASESPEO.GRADE_ANNUALIZATION_FACTOR,

SALARYBASESPEO.CREATED_BY SALARY_BASIS_CREATED_BY ,

SALARYBASESPEO.CREATION_DATE SALARY_BASIS_CREATION_DATE,

SALARYBASESPEO.LAST_UPDATE_DATE SALARY_BASIS_LAST_UPDATE_DATE,

SALARYBASESPEO.LAST_UPDATED_BY SALARY_BASIS_LAST_UPDATED_BY,

SALARYBASESPEO.STATUS,

SALARYBASESPEO.SALARY_BASIS_TYPE,

SALARYBASESPEO.AMOUNT_DECIMAL_PRECISION,

SALARYBASESPEO.AMOUNT_ROUNDING_CODE,

SALARYBASESPEO.ANNUAL_ROUNDING_CODE,

SALARYBASESPEO.RANGE_ROUNDING_CODE,

SALARYBASESPEO.CALCULATION_MODE,

InputValueTranslationPEO.Name INPUT_VALUE_NAME,

InputValueTranslationPEO.Language INPUT_VALUE_LANGUAGE,

InputValueTranslationPEO.Source_Lang,

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

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

From Cmp_salary SALARYPEO, pay_input_values_tl InputValueTranslationPEO,CMP_SALARY_BASES SALARYBASESPEO,CMP_SALARY_BASES_TL SALARYBASESTLPEO

Where SALARYPEO.SALARY_BASIS_ID = SALARYBASESPEO.SALARY_BASIS_ID

AND SALARYBASESPEO.INPUT_VALUE_ID = InputValueTranslationPEO.INPUT_VALUE_ID(+)

AND InputValueTranslationPEO.LANGUAGE(+) = USERENV('LANG')

AND SALARYBASESPEO.SALARY_BASIS_ID = SALARYBASESTLPEO.SALARY_BASIS_ID

AND SALARYBASESTLPEO.LANGUAGE = USERENV('LANG')

AND SALARYBASESTLPEO.BUSINESS_GROUP_ID = SALARYBASESPEO.BUSINESS_GROUP_ID