PAY_HDL_RATES

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
RATE_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE RATE_DEFINITION_ID PAYROLL_RELATIONSHIP_ID PAYROLL_TERM_ID PAYROLL_ASSIGNMENT_ID NAME BASE_NAME SHORT_NAME LEGISLATIVE_DATA_GROUP_ID LEGISLATION_CODE ASSIGNMENT_ID TERM_ID BASE_SALARY_FLAG ELEMENT_TYPE_ID DIR_CARD_COMP_DEF_ID FTE_FLAG OVERALL_SALARY_FLAG AMOUNT_ENTERABLE FACTOR_ENTERABLE RATE_TYPE PERIODICITY CURRENCY AMOUNT FACTOR INPUT_VALUE_ID

Name

RATE_ID

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

RATE_DEFINITION_ID

PAYROLL_RELATIONSHIP_ID

PAYROLL_TERM_ID

PAYROLL_ASSIGNMENT_ID

NAME

BASE_NAME

SHORT_NAME

LEGISLATIVE_DATA_GROUP_ID

LEGISLATION_CODE

ASSIGNMENT_ID

TERM_ID

BASE_SALARY_FLAG

ELEMENT_TYPE_ID

DIR_CARD_COMP_DEF_ID

FTE_FLAG

OVERALL_SALARY_FLAG

AMOUNT_ENTERABLE

FACTOR_ENTERABLE

RATE_TYPE

PERIODICITY

CURRENCY

AMOUNT

FACTOR

INPUT_VALUE_ID

Query

SQL_Statement
select pee.element_entry_id rate_id, pee.effective_start_date, pee.effective_end_date, prd.rate_definition_id, peu.payroll_relationship_id, peu.payroll_term_id, peu.payroll_assignment_id, prd.name, prd.base_name, prd.short_name, prd.legislative_data_group_id, prd.legislation_code, prg_asg.assignment_id, prg_term.term_id, nvl(prd.BASE_SALARY_FLAG, 'N') Base_salary_flag, prd.ELEMENT_TYPE_ID, prd.DIR_CARD_COMP_DEF_ID, nvl(prd.FTE_FLAG, 'N') fte_flag, nvl(prd.OVERALL_SALARY_FLAG, 'N') overall_salary_flag, nvl(prd.AMOUNT_ENTERABLE, 'N') amount_enterable, nvl(prd.FACTOR_ENTERABLE, 'N') factor_enterable, prd.type rate_type, nvl(prd.DEFAULT_RTN_PERIODICITY, prd.default_calc_periodicity) periodicity, prd.default_rtn_currency currency, AMT.amount, FACTOR.factor, nvl(AMT.input_value_id, FACTOR.input_value_id) input_value_id from fusion.pay_element_entries_f pee, fusion.pay_element_types_f pet, fusion.pay_entry_usages peu, fusion.pay_rate_definitions_f_vl prd, fusion.pay_rel_groups_dn prg_term, fusion.pay_rel_groups_dn prg_asg , (select eev_amount.element_entry_id, eev_amount.screen_entry_value amount, eev_amount.input_value_id, eev_amount.effective_start_date, eev_amount.effective_end_date from fusion.PAY_INPUT_VALUES_F iv_amount, fusion.PAY_ELEMENT_ENTRY_VALUES_F eev_amount where 'RESERVED_INPUT_VALUE' = iv_amount.reserved_input_value and iv_amount.input_value_id = eev_amount.input_value_id ) AMT, (select eev_factor.element_entry_id, eev_factor.screen_entry_value factor, eev_factor.input_value_id, eev_factor.effective_start_date, eev_factor.effective_end_date from fusion.PAY_INPUT_VALUES_F iv_amount, fusion.PAY_ELEMENT_ENTRY_VALUES_F eev_factor where 'RESERVED_INPUT_FACTOR' = iv_amount.reserved_input_value and iv_amount.input_value_id = eev_factor.input_value_id ) FACTOR where pee.element_entry_id = peu.element_entry_id and prd.element_type_id = pet.element_type_id and pee.element_type_id = pet.element_type_id and prd.type = 'ET' and prd.element_type_id is not null and pee.effective_start_date between prd.effective_start_date and prd.effective_end_date and prg_term.group_type (+) = 'T' and peu.payroll_term_id = prg_term.relationship_group_id (+) and prg_asg.group_type (+) = 'A' and peu.payroll_assignment_id = prg_asg.relationship_group_id (+) and pee.element_entry_id = AMT.element_entry_id (+) and pee.effective_start_date = AMT.effective_start_date (+) and pee.effective_end_date = AMT.effective_end_date (+) and pee.element_entry_id = FACTOR.element_entry_id (+) and pee.effective_start_date = FACTOR.effective_start_date (+) and pee.effective_end_date = FACTOR.effective_end_date (+)

SQL_Statement

select pee.element_entry_id rate_id,

pee.effective_start_date,

pee.effective_end_date,

prd.rate_definition_id,

peu.payroll_relationship_id,

peu.payroll_term_id,

peu.payroll_assignment_id,

prd.name,

prd.base_name,

prd.short_name,

prd.legislative_data_group_id,

prd.legislation_code,

prg_asg.assignment_id,

prg_term.term_id,

nvl(prd.BASE_SALARY_FLAG, 'N') Base_salary_flag,

prd.ELEMENT_TYPE_ID,

prd.DIR_CARD_COMP_DEF_ID,

nvl(prd.FTE_FLAG, 'N') fte_flag,

nvl(prd.OVERALL_SALARY_FLAG, 'N') overall_salary_flag,

nvl(prd.AMOUNT_ENTERABLE, 'N') amount_enterable,

nvl(prd.FACTOR_ENTERABLE, 'N') factor_enterable,

prd.type rate_type,

nvl(prd.DEFAULT_RTN_PERIODICITY, prd.default_calc_periodicity) periodicity,

prd.default_rtn_currency currency,

AMT.amount,

FACTOR.factor,

nvl(AMT.input_value_id, FACTOR.input_value_id) input_value_id

from fusion.pay_element_entries_f pee,

fusion.pay_element_types_f pet,

fusion.pay_entry_usages peu,

fusion.pay_rate_definitions_f_vl prd,

fusion.pay_rel_groups_dn prg_term,

fusion.pay_rel_groups_dn prg_asg ,

(select eev_amount.element_entry_id,

eev_amount.screen_entry_value amount,

eev_amount.input_value_id,

eev_amount.effective_start_date,

eev_amount.effective_end_date

from fusion.PAY_INPUT_VALUES_F iv_amount,

fusion.PAY_ELEMENT_ENTRY_VALUES_F eev_amount

where 'RESERVED_INPUT_VALUE' = iv_amount.reserved_input_value

and iv_amount.input_value_id = eev_amount.input_value_id

) AMT,

(select eev_factor.element_entry_id,

eev_factor.screen_entry_value factor,

eev_factor.input_value_id,

eev_factor.effective_start_date,

eev_factor.effective_end_date

from fusion.PAY_INPUT_VALUES_F iv_amount,

fusion.PAY_ELEMENT_ENTRY_VALUES_F eev_factor

where 'RESERVED_INPUT_FACTOR' = iv_amount.reserved_input_value

and iv_amount.input_value_id = eev_factor.input_value_id

) FACTOR

where pee.element_entry_id = peu.element_entry_id

and prd.element_type_id = pet.element_type_id

and pee.element_type_id = pet.element_type_id

and prd.type = 'ET'

and prd.element_type_id is not null

and pee.effective_start_date between prd.effective_start_date and prd.effective_end_date

and prg_term.group_type (+) = 'T'

and peu.payroll_term_id = prg_term.relationship_group_id (+)

and prg_asg.group_type (+) = 'A'

and peu.payroll_assignment_id = prg_asg.relationship_group_id (+)

and pee.element_entry_id = AMT.element_entry_id (+)

and pee.effective_start_date = AMT.effective_start_date (+)

and pee.effective_end_date = AMT.effective_end_date (+)

and pee.element_entry_id = FACTOR.element_entry_id (+)

and pee.effective_start_date = FACTOR.effective_start_date (+)

and pee.effective_end_date = FACTOR.effective_end_date (+)